Hi All,

I'm trying to join three tables to present a summary view on the Rent Received, Payment to Supplier and Repair Cost for a property. I have attached a sample data and the view I'm trying to achieve here: https://app.box.com/s/07f9l1xyz8yioi5nv36omf0y9wxqhq6u

I have suppliercode, prop code, month and year as common columns in the tables. When I join the tables, for some months the amounts do not appear and in certain cases the amount aggregate.

Thanks

Jag

Try PIVOT or Dynamic PIVOT and UNION the 3 result set. Below is an example of using PIVOT to display summary of payment to supplier.

SELECT * FROM (
SELECT [Year]
      ,[Month]
      ,[PROP_CODE]
      ,[SupplierCode]
      ,isnull(TotalPayment,0) as [TotalPayment]
      ,[ControlGroup]
	  ,'Supplier Payment' as [Supplier Payment]
  FROM [SQL2012EELearning].[dbo].[SupplierPayment]
  ) X
PIVOT
( SUM(TotalPayment) FOR [Month] IN (January,February,March,April,May,June,July,August,September,November,December)) pvt

You can append year and month to form a column. i.e FOR [Month] IN (January_2014,February_2014

You need to UNION in the below way. Please note the number of Select columns in each query.

SELECT [SupplierCode]
      ,[Prop Code]
	  ,'Rent Received'  AS [FEE TYPE] 
      ,[TotalPayment] AS [AMOUNT]
      ,[Year]
      ,[Month]
  FROM [SQL2012EELearning].[dbo].[CustomerPayment]

UNION ALL

SELECT (SELECT TOP 1 [SupplierCode] FROM [SQL2012EELearning].[dbo].[CustomerPayment] A WHERE A.[PROP CODE]=B.[PROP_CODE]) AS [SupplierCode]
      ,[PROP_CODE] as [Prop Code]
	  ,'Repair Cost'  AS [FEE TYPE] 
      ,[AMOUNT]
	  ,[Year]
      ,[Month]
  FROM [SQL2012EELearning].[dbo].[RepairCost] B

UNION ALL

SELECT [SupplierCode]
      ,[PROP_CODE] as [Prop Code]
	  ,'Supplier Payment' as [FEE TYPE]
      ,[TotalPayment]
	  ,[Year]
	  ,[Month]
  FROM [SQL2012EELearning].[dbo].[SupplierPayment]

Thanks Rajen, I never knew we could this directly in Sql. I was planning to join the tables and then Pivot them in excel. The pivot covers only one field, I like to have supplierpayment, rentreceived, repaircost and lettingfee in the pivot and not sure how to do this using multiple tables. I have updated the sample excel file which you will have to download again and if you can help me with the query please.

Thanks a ton :)

You can try this one. The format will not be exactly same as when you create in Excel.

DECLARE @YEAR INT = 2014
;WITH CTESupplier AS (
	SELECT
	   [Month] 
      ,[PROP_CODE]
      ,isnull(TotalPayment,0) as [TotalPayment]
	  ,'Supplier Payment' as [Fee Type]
  FROM [SQL2012EELearning].[dbo].[SupplierPayment] WHERE YEAR = @YEAR
), CTESupplier_Result AS (
	SELECT * FROM CTESupplier
	PIVOT
	(SUM(TotalPayment) FOR [Month] IN (January,February,March,April,May,June,July,August,September,November,December)) pvt
), CTERepairCost AS (
	SELECT [Month]
      ,[PROP_CODE]
	  ,'Repair Cost' as [Fee Type]
      ,isnull([AMOUNT],0) as [AMOUNT]
  FROM [SQL2012EELearning].[dbo].[RepairCost] WHERE YEAR = @YEAR
), CTERepairCost_Result AS (
	SELECT * FROM CTERepairCost
	PIVOT
	(SUM([AMOUNT]) FOR [Month] IN (January,February,March,April,May,June,July,August,September,November,December)) pvt
), CTERentReceived AS (
	SELECT 
      [Prop Code]
      ,isnull([TotalPayment],0) as [TotalPayment]
      ,[Month]
	  ,'Rent Received' as [Fee Type]
  FROM [SQL2012EELearning].[dbo].[CustomerPayment] WHERE YEAR = @YEAR
), CTERentReceived_Result AS (
	SELECT * FROM CTERentReceived
	PIVOT
	(SUM([TotalPayment]) FOR [Month] IN (January,February,March,April,May,June,July,August,September,November,December)) pvt
) , CTEFinal AS (
	SELECT * FROM CTESupplier_Result
	UNION ALL
	SELECT * FROM CTERepairCost_Result
	UNION ALL
	SELECT * FROM CTERentReceived_Result
)
SELECT * FROM CTEFinal ORDER BY PROP_CODE

Thanks but what if I want to just Union or Join these table. How will that query work?

regards,

jag

Well In that case you need to manipulate in excel by creating PIVOT. If you are good in Excel you can go for it.

Could you help me how would I use UNION All here please as like to see the data in this form so I can call the SQL view in excel

SupplierCode, PropCode, LettingFee, Rent Received, SupplierPayment, Repaircost, Month, Year

Hope the below is correct

select SupplierCode, PROP_CODE As PropCode, ISNULL(Null,0) As LettingFee, ISNULL(Null,0) As RentReceived, TotalPayment as SupplierPayment, ISNULL(Null,0) As RepairCost, Month, Year
FROM suppliertable

Union All

select [Supplier Code], PROP_CODE, TRANS_AMOUNT, isNull(Null,0), isNull(Null,0),isNull(Null,0), Month, Year  from lettingtable

Union All

select SupplierCode, [Prop Code], isNull(Null,0), Total, isNull(Null,0),isNull(Null,0), Month, Year from customerpaymenttable

Union All

select SupplierCode, PROP_CODE, isNull(Null,0),isNull(Null,0),isNull(Null,0), (AMOUNT-VAT_AMOUNT) as RepairCost, Month, Year from repairtable

Regards,
J.Singh

• Edited by jaggy99 Saturday, January 31, 2015 7:15 AM