Skip to Content
0
Jun 14, 2016 at 05:35 AM

Customerwise product sold

21 Views

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