Customerwise product sold

[former_member186803]

Hi All,

I would like to create a query which gives me the list of all my customers along with products sold to them and if no product is sold it will appear with zero values.

My query is as bellow...

SELECT T3.[Cardcode],T3.[CardName],T1.[ItemCode], T1.[itemname],T1.[Quantity],T1.[LineTotal]

FROM OCRD T3

LEFT JOIN OINV T0 ON T3.CARDCODE = T0.CARDCODE

LEFT JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[DocDate] >={?fromdate}

and  T0.[DocDate] <={?todate}

and T1.[WhsCode] not in ('07','M7','08','M8','D1','MG','06','M6')

and T1.[ItemCode] like 'CPL%'

union all

SELECT T3.[Cardcode],T3.[CardName],T1.[ItemCode], T1.[itemname],T1.[Quantity],T1.[LineTotal]

FROM OCRD T3

LEFT JOIN ORIN T0 ON T3.CARDCODE = T0.CARDCODE

LEFT JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[DocDate] >={?fromdate} and 

T0.[DocDate] <={?todate}

and T1.NoInvtryMv = 'N'

and T1.[WhsCode] not in ('07','M7','08','M8','D1','MG','06','M6')

and T1.[ItemCode] like 'CPL%'

This gives me only the list of customers where there is sales, I need all customers to appear in the list.

Your help is appreciated.

regards

Suman

[Johan_H]

Hi Suman,

Please give this a try:

SELECT T3.[Cardcode]

      ,T3.[CardName]

      ,ISNULL(X.[ItemCode], '') AS ItemCode

      ,ISNULL(X.[ItemName], '') AS IemName

      ,SUM(ISNULL(X.[Quantity], 0)) AS Quantity

      ,SUM(ISNULL(X.[LineTotal], 0)) AS LineTotal

FROM OCRD T3

LEFT OUTER JOIN

(SELECT T1.[ItemCode], T1.[Dscription] AS ItemName,T1.[Quantity],T1.[LineTotal]

FROM OINV T0

      INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[DocDate] >={?fromdate}

  and  T0.[DocDate] <={?todate}

  and T1.[WhsCode] not in ('07','M7','08','M8','D1','MG','06','M6')

  and T1.[ItemCode] like 'CPL%'

union all

SELECT T1.[ItemCode], T1.[Dscription],T1.[Quantity],T1.[LineTotal]

FROM ORIN T0

LEFT JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[DocDate] >={?fromdate} and

T0.[DocDate] <={?todate}

and T1.NoInvtryMv = 'N'

and T1.[WhsCode] not in ('07','M7','08','M8','D1','MG','06','M6')

and T1.[ItemCode] like 'CPL%') X ON T3.CardCode = X.CardCode

GROUP BY T3.[Cardcode]

      ,T3.[CardName]

      ,ISNULL(X.[ItemCode], '')

      ,ISNULL(X.[ItemName], '')

Regards,

Johan

[former_member186803]

Hi Johan,

Thanks for your help.

I have managed to frame the query which is almost same like yours.

as bellow and it is giving the expected output now .

SELECT a.[Cardcode],a.[CardName],b.itemcode, b.Dscription,b.Quantity,b.LineTotal

FROM OCRD a

left outer join

(

SELECT T3.[Cardcode],T3.[CardName],T1.[ItemCode], T1.[Dscription],T1.[Quantity],T1.[LineTotal]

FROM OCRD T3

LEFT JOIN OINV T0 ON T3.CARDCODE = T0.CARDCODE

LEFT JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[DocDate] >={?fromdate}

and  T0.[DocDate] <={?todate}

and T1.[WhsCode] not in ('07','M7','08','M8','D1','MG','06','M6')

and T1.[ItemCode] like 'CPL%'

union all

SELECT T3.[Cardcode],T3.[CardName],T1.[ItemCode], T1.[Dscription],T1.[Quantity],T1.[LineTotal]

FROM OCRD T3

LEFT JOIN ORIN T0 ON T3.CARDCODE = T0.CARDCODE

INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[DocDate] >={?fromdate}

and  T0.[DocDate] <={?todate}

and T1.NoInvtryMv = 'N'

and T1.[WhsCode] not in ('07','M7','08','M8','D1','MG','06','M6')

and T1.[ItemCode] like 'CPL%'

) b

on b.cardcode=a.cardcode

where a.CardType = 'C'