cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

need Query Help

Go to solution
former_member541807
Active Contributor

on ‎03-31-2015 7:56 AM

0 Kudos

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

Show replies
Show replies
Answer

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
‎03-31-2015 12:24 PM
0 Kudos

Dear Avelino,

Please check below Query.

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 OINM T5 on T5.ItemCode = T1.ItemCode

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 T5.DocDate <  '[%1]'

Hope this helps

--

--

Regards::::

Atul Chakraborty

Show replies
Show replies
former_member541807
Active Contributor
‎04-07-2015 2:00 AM
0 Kudos

Hi,

thanks Atul Chakraborty. you got i right.

@Nagarajan thanks for your input, really appreciate your help

@ Manish K thanks for your input, really appreciate your help

Fidel

Answers (2)

Answers (2)

former_member184146
Active Contributor
‎04-02-2015 3:05 PM
0 Kudos

Hi,

use this

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 OINM T5 on T5.ItemCode = T1.ItemCode

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 T5.DocDate <  '[%1]'

Show replies
Show replies
kothandaraman_nagarajan
Active Contributor
‎04-02-2015 2:52 PM
0 Kudos

Hi,

Try this query:

SE 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 OINM T4 on t4.itemcode = T0.itemcode

WHERE T0.ONHAND >0  AND T0.WhsCode = '[%0]'  and t4.InQty > 0 and t4.OutQty = 0 and T4.docdate < [%1]

Thanks & Regards,

Nagarajan

Show replies
Show replies
Ask a Question