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