Skip to Content
0
Former Member
Dec 05, 2009 at 07:18 AM

Query Customer ON/OFF

28 Views

hai Expert

how to write a query that displays the

My concern is that if only to report only items handed over by invoice ..

1. Can you put the "OFF" in ON / OFF COLOUM if the "customer" NOT order goods within 3 weeks and,

2. Can you put the "ON" in the ON / OFF COLOUM if the "customer" goods orders within 3 weeks

it will look like this:

SELECT T0.[Dscription] as 'Item Name', T0.[U_NewDesc] as 'Description',T1.[CardName], T0.[PriceBefDi],T0.[unitMsr], T0.[DocDate]as 'Last Order Date',

(T0.[Dscription]T0.[U_NewDesc])/DAY(GetDate()) Case WHEN Month(GetDate()) in (1,3,5,7,8,10,12) THEN 31 WHEN Month(GetDate()) in (2,4,6,9,11) THEN 3 ELSE 3 END))AS 'ON/OFF active'

FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[CardName] =[%0] and T0.[DocDate] >=[%1] and T0.[DocDate]<=[%2] and Month(T0.DocDate) = Month(GetDate()) AND Year(T0.DocDate) = Year(GetDate())

AND (T0.[Dscription]*T0.[U_NewDesc])>0

GROUP BY T0.[Dscription] ,T0.[U_NewDesc],T1.[CardName],T0.[PriceBefDi],T0.[unitMsr], T0.[DocDate]

ORDER BY T0.[Dscription] ,T0.[U_NewDesc],T1.[CardName],T0.[PriceBefDi],T0.[unitMsr], T0.[DocDate]

it will look like this:

Item Name, Description, Customer/Vendor Name, Unit Price, Unit, Last Order Date, Active/No active,

AAA, AAA, XXX, $15, KG, Active,

BBB,BB, ZZZ,$20,KG,Not Active,

thx for all