Skip to Content

Query Report

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Dec 14, 2018 at 07:48 AM

    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


    Add comment
    10|10000 characters needed characters exceeded

  • Dec 14, 2018 at 01:26 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Dec 15, 2018 at 06:31 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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
  • Dec 17, 2018 at 11:32 AM

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

    Add comment
    10|10000 characters needed characters exceeded