Query Report

[former_member573912]

select cardname,sum(DocTotal)AS SALES from OINV

where Canceled='n' and DocDate>='20180401' and DocDate <='20180430'

group by cardname

------------------------------

select cardname,sum(DocTotal) AS PAYMENTRECVED from ORCT

where Canceled='n' and DocDate>='20180401' and DocDate <='20180430'

group by cardname

i want to combine both queries and required as n below format :-

cardname, sales , payment received

[azizelmir · Accepted solution]

Hi qwer G,

This is the requested query:

Select T1.Cardcode,T1.Cardname,T1.Sales,T2.PAYMENTRECVED FROM

(select Cardcode,cardname,sum(DocTotal) AS SALES from OINV where Canceled='N' and DocDate>='20180401' and DocDate <='20180430' group by cardname, cardcode ) T1 LEFT OUTER JOIN

(select CardCode,cardname,sum(DocTotal) AS PAYMENTRECVED from ORCT where Canceled='n' and DocDate>='20180401' and DocDate <='20180430' group by cardname, cardcode) T2

ON T1.Cardcode=T2.Cardcode

[former_member573912]

In row level customer name is coming 5 to 7 times.It is repetitive

[former_member573912]

Thanks for your reply, your query is correct , i want it in different format .

CardCode ,Cardname, January_TotalSales, January Payment Received, FeburaryTotal Sales, Feb Payment received, March Total sales, March Payment Received ,AprilTotal Sales, April Payment received,MayTotal Sales, May Payment received,JuneTotal Sales, JunePayment received,

upto december

Please can you give in above format

[azizelmir]

SELECT  w1.[CardCode],w1.[Cardname],
		w1.[Jan_TotalSales], w2.[Jan_PaymentReceived],
		w1.[Feb_TotalSales], w2.[Feb_PaymentReceived],
		w1.[Mar_TotalSales], w2.[Mar_PaymentReceived],
		w1.[Apr_TotalSales], w2.[Apr_PaymentReceived],
		w1.[May_TotalSales], w2.[May_PaymentReceived],
		w1.[June_TotalSales], w2.[June_PaymentReceived],
		w1.[July_TotalSales], w2.[July_PaymentReceived],
		w1.[Aug_TotalSales], w2.[Aug_PaymentReceived],
		w1.[Sept_TotalSales], w2.[Sept_PaymentReceived], 
		w1.[Oct_TotalSales], w2.[Oct_PaymentReceived],
		w1.[Nov_TotalSales], w2.[Nov_PaymentReceived], 
		w1.[Dec_TotalSales] , w2.[Dec_PaymentReceived]


FROM
(Select [CardCode] as 'CardCode', [CardName] as 'CardName',  
	isnull([1],0) as Jan_TotalSales, isnull([2],0) as Feb_TotalSales, isnull([3],0) as Mar_TotalSales, isnull([4],0) as Apr_TotalSales, isnull([5],0) as May_TotalSales, isnull([6],0) as June_TotalSales, isnull([7],0) as July_TotalSales, isnull([8],0) as Aug_TotalSales, isnull([9],0) as Sept_TotalSales, isnull([10],0) as Oct_TotalSales, isnull([11],0) as Nov_TotalSales, isnull([12],0) as Dec_TotalSales

from (
Select		T1.Cardcode as 'CardCode',
			T1.[BP Name] as 'CardName',
			sum(T1.Sales) as T,
			isnull(Sum(T2.PAYMENTRECVED),0) as X,
			T1.[Month]
			
		FROM 
		(select Month(Docdate) as 'Month',Cardcode, (select CardName from OCRD where OCRD.CardCode=OINV.CardCode) as 'BP Name', sum(DocTotal) AS SALES from OINV where Canceled='N' and DocDate>='20180101' and DocDate <='20181231' group by cardcode,Month(DocDate) 
) T1 LEFT OUTER JOIN 
(select Month(DocDate) as 'Month',CardCode, (select CardName from OCRD where OCRD.CardCode=ORCT.CardCode) as 'BP Name' , sum(DocTotal) AS PAYMENTRECVED from ORCT where Canceled='N' and DocDate>='20180101' and DocDate <='20181231' 
Group by cardcode, Month(DocDate)
) T2 
ON T1.Cardcode=T2.Cardcode 
Group By T1.CardCode,T1.[BP Name],T1.[Month]
)S pivot (sum(T) for month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))P
) W1 
inner join
(Select [CardCode] as 'CardCode', [CardName] as 'CardName', isnull([1],0) as Jan_PaymentReceived, isnull([2],0) as Feb_PaymentReceived, isnull([3],0) as Mar_PaymentReceived, isnull([4],0) as Apr_PaymentReceived, isnull([5],0) as May_PaymentReceived, isnull([6],0) as June_PaymentReceived, isnull([7],0) as July_PaymentReceived, isnull([8],0) as Aug_PaymentReceived, isnull([9],0) as Sept_PaymentReceived, isnull([10],0) as Oct_PaymentReceived, isnull([11],0) as Nov_PaymentReceived, isnull([12],0) as Dec_PaymentReceived

from (Select		T1.Cardcode as 'CardCode',
			T1.[BP Name] as 'CardName',
			sum(T1.Sales) as T,
			isnull(Sum(T2.PAYMENTRECVED),0) as X,
			T1.[Month]
			
		FROM (select Month(Docdate) as 'Month',Cardcode, (select CardName from OCRD where OCRD.CardCode=OINV.CardCode) as 'BP Name', sum(DocTotal) AS SALES from OINV where Canceled='N' and DocDate>='20180101' and DocDate <='20181231' group by cardcode,Month(DocDate) 
) T1 LEFT OUTER JOIN 
(select Month(DocDate) as 'Month',CardCode, (select CardName from OCRD where OCRD.CardCode=ORCT.CardCode) as 'BP Name' , sum(DocTotal) AS PAYMENTRECVED from ORCT where Canceled='N' and DocDate>='20180101' and DocDate <='20181231' Group by cardcode, Month(DocDate)
) T2 
ON T1.Cardcode=T2.Cardcode 
Group By T1.CardCode,T1.[BP Name],T1.[Month]
)S pivot (sum(X) for month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))P
) W2 ON W1.Cardcode=w2.CardCode

[former_member573912]

Thanks for your query .It worked.

If I want the same thing below format :-

nov sales amount nov payment received dec sales amount dec payment recieved

It is the same as the above query but reqd to add one more month in the colum.

Thanks in advance

[azizelmir]

As requested:

Select DATENAME(month, DATEADD(month, T1.Month-1, CAST('2008-01-01' AS datetime))) as 'Month' ,T1.Cardcode,T1.[BP Name],sum(T1.Sales) as 'Total Sales',isnull(Sum(T2.PAYMENTRECVED),0) as 'Total Payment Recieved' FROM (select Month(Docdate) as 'Month',Cardcode, (select CardName from OCRD where OCRD.CardCode=OINV.CardCode) as 'BP Name', sum(DocTotal) AS SALES from OINV where Canceled='N' and DocDate>='20180101' and DocDate <='20181231' group by cardcode,Month(DocDate) ) T1 LEFT OUTER JOIN (select Month(DocDate) as 'Month',CardCode, (select CardName from OCRD where OCRD.CardCode=ORCT.CardCode) as 'BP Name' ,sum(DocTotal) AS PAYMENTRECVED from ORCT where Canceled='N' and DocDate>='20180101' and DocDate <='20181231' group by cardcode, Month(DocDate)) T2 ON T1.Cardcode=T2.Cardcode Group By T1.CardCode,T1.[BP Name],T1.[Month]

Regards,

Aziz