Skip to Content
0
Mar 31, 2015 at 06:56 AM

need Query Help

11 Views

Hi,

i have this Query working well

SELECT distinct T0.ITEMCODE, T4.DocDate AS 'Last Received Date', T1.itemName,T0.ONHAND as 'Total Qty',
CASE WHEN Datediff (day,T4.DocDate, Getdate ()) < 365 THEN T0.ONHAND END '< 1 Year (Qty)',
CASE WHEN Datediff (day,T4.DocDate, Getdate ()) > 365 AND Datediff (day,T1.LastPurDat, Getdate ()) < 730 THEN T0.ONHAND END '< 2 Years (Qty)',
CASE WHEN Datediff (day,T4.DocDate, Getdate ()) > 730 AND Datediff (day,T1.LastPurDat, Getdate ()) < 1095 THEN T0.ONHAND END '< 3 Years (Qty)',
CASE WHEN Datediff (day,T4.DocDate, Getdate ()) > 1095 AND Datediff (day,T1.LastPurDat, Getdate ()) < 1460 THEN T0.ONHAND END '< 4 Years (Qty)',
CASE WHEN Datediff (day,T4.DocDate, Getdate ()) > 1460 AND Datediff (day,T1.LastPurDat, Getdate ()) < 1825 THEN T0.ONHAND END '< 5 Years (Qty)',
CASE WHEN Datediff (day,T4.DocDate, Getdate ()) > 1825 AND Datediff (day,T1.LastPurDat, Getdate ()) < 2190 THEN T0.ONHAND END '< 6 Years Days (Qty)',
CASE WHEN Datediff (day,T4.DocDate, Getdate ()) > 2190 AND Datediff (day,T1.LastPurDat, Getdate ()) < 2555 THEN T0.ONHAND END '< 7 Years (Qty)',
CASE WHEN Datediff (day,T4.DocDate, Getdate ()) > 2555 AND Datediff (day,T1.LastPurDat, Getdate ()) < 2920 THEN T0.ONHAND END '< 8 Years (Qty)',
CASE WHEN Datediff (day,T4.DocDate, Getdate ()) > 2920 AND Datediff (day,T1.LastPurDat, Getdate ()) < 3285 THEN T0.ONHAND END '< 9 Years (Qty)',
CASE WHEN Datediff (day,T4.DocDate, Getdate ()) > 3285 AND Datediff (day,T1.LastPurDat, Getdate ()) < 3650 THEN T0.ONHAND END '< 10 Years (Qty)',
CASE WHEN Datediff (day,T4.DocDate, Getdate ()) > 3650 AND Datediff (day,T1.LastPurDat, Getdate ()) < 4015 THEN T0.ONHAND END '> 11 Years (Qty)',


CASE WHEN Datediff (day,T4.DocDate, Getdate ()) > 4015 THEN T0.ONHAND END '> 11 Years (Qty)'
FROM OITW T0 INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE
INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD
left join ibt1 t3 on t3.itemcode = t0.whscode
left join (SELECT ItemCode, Max(DocDate)  DocDate FROM OINM T0 WHERE InQty > 0 and OutQty = 0 group by ItemCode) T4 ON T4.ItemCode = T0.ItemCode
WHERE T0.ONHAND >0  AND T0.WhsCode = '[%0]'

and when i add this line in the were clause

and T4.DocDate < '[%1]'

it gives me an error Incorrect Syntax near 'USER'

please help me figure out where i miss things out.

thanks.

Fidel