Skip to Content
0
Oct 29, 2012 at 11:24 AM

Aging Report With Parameter as Item Property

152 Views

Hi Guys,

I have Generated a Query For Stock Aging With Parameter as Warehouse and Now i want to Add Additional Parameters as Item Group and Item Property and Struggling while Passing the Parameter as Item Property.

Any One Pls Give me Some Ideas for Generating the Query.

Query 1 :


SELECT T0.ItemCode, T1.itemName,T2.[ItmsGrpNam],T0.WhsCode 'Warehouse',T0.ONHAND as 'In Stock',

T1.QryGroup1,

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ()) <45 THEN T0.ONHAND END '<45 Days (Qty)',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>45 AND Datediff (day,T1.LastPurDat, Getdate ()) <90 THEN T0.ONHAND END '<45 T0 90 Days (Qty)',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>90 AND Datediff (day,T1.LastPurDat, Getdate ()) <135 THEN T0.ONHAND END '<90 T0 135 Days (Qty)',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>135 AND Datediff (day,T1.LastPurDat, Getdate ()) <180 THEN T0.ONHAND END '<135 T0 180 Days (Qty)',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>180 AND Datediff (day,T1.LastPurDat, Getdate ()) <225 THEN T0.ONHAND END '<180 T0 225 Days (Qty)',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>225 AND Datediff (day,T1.LastPurDat, Getdate ()) <270 THEN T0.ONHAND END '<225 T0 270 Days (Qty)',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>270 AND Datediff (day,T1.LastPurDat, Getdate ()) <315 THEN T0.ONHAND END '<270 T0 315 Days (Qty)',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>315 AND Datediff (day,T1.LastPurDat, Getdate ()) <360 THEN T0.ONHAND END '<315 T0 360 Days (Qty)',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ()) >360 THEN T0.ONHAND END '>360 Days (Qty)'

FROM OITW T0 INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE

INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD

WHERE T0.ONHAND >0 AND T0.WhsCode = '[%0]' and T2.ItmsGrpNam='[%1]'

Query 2 :

Based on the Posts on the Forum Tried Modifying the Above Query as Below,

SELECT ItemCode,ItemName,ItemGroup,Warehouse,InStock,45Days,45&90Days,90&135Days,Property

From(SELECT 1 Property,

(Case When T1.QryGroup1='Y' Then (Select ItmsGrpNam From OITG Where ItmsTypCod=1) Else ' ' END) As Property,

SELECT T0.ItemCode ItemCode, T1.itemName ItemName ,T2.[ItmsGrpNam] ItemGroup,T0.WhsCode 'Warehouse',T0.ONHAND as 'InStock',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ()) <45 THEN T0.ONHAND END '45Days',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>45 AND Datediff (day,T1.LastPurDat, Getdate ()) <90 THEN T0.ONHAND END '45&90',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>90 AND Datediff (day,T1.LastPurDat, Getdate ()) <135 THEN T0.ONHAND END '90&135Days',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>135 AND Datediff (day,T1.LastPurDat, Getdate ()) <180 THEN T0.ONHAND END '135&180Days',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>180 AND Datediff (day,T1.LastPurDat, Getdate ()) <225 THEN T0.ONHAND END '180&225Days',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>225 AND Datediff (day,T1.LastPurDat, Getdate ()) <270 THEN T0.ONHAND END '225&270Days',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>270 AND Datediff (day,T1.LastPurDat, Getdate ()) <315 THEN T0.ONHAND END '270&315Days',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>315 AND Datediff (day,T1.LastPurDat, Getdate ()) <360 THEN T0.ONHAND END '315&360Days',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ()) >360 THEN T0.ONHAND END '>360Days'

FROM OITW T0

INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE

INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD

WHERE T0.ONHAND >0 and T1.QryGroup1='Y'

Union

(SELECT 2 Property,

(Case When T1.QryGroup1='Y' Then (Select ItmsGrpNam From OITG Where ItmsTypCod=2) else '' END) As Property,

SELECT T0.ItemCode ItemCode, T1.itemName ItemName ,T2.[ItmsGrpNam] ItemGroup,T0.WhsCode 'Warehouse',T0.ONHAND as 'InStock',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ()) <45 THEN T0.ONHAND END '45Days',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>45 AND Datediff (day,T1.LastPurDat, Getdate ()) <90 THEN T0.ONHAND END '45&90',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>90 AND Datediff (day,T1.LastPurDat, Getdate ()) <135 THEN T0.ONHAND END '90&135Days',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>135 AND Datediff (day,T1.LastPurDat, Getdate ()) <180 THEN T0.ONHAND END '135&180Days',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>180 AND Datediff (day,T1.LastPurDat, Getdate ()) <225 THEN T0.ONHAND END '180&225Days',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>225 AND Datediff (day,T1.LastPurDat, Getdate ()) <270 THEN T0.ONHAND END '225&270Days',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>270 AND Datediff (day,T1.LastPurDat, Getdate ()) <315 THEN T0.ONHAND END '270&315Days',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ())>315 AND Datediff (day,T1.LastPurDat, Getdate ()) <360 THEN T0.ONHAND END '315&360Days',

CASE WHEN Datediff (day,T1.LastPurDat, Getdate ()) >360 THEN T0.ONHAND END '>360Days'

FROM OITW T0

INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE

INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD

WHERE T0.ONHAND >0 and T1.QryGroup2='Y'

)X

Regards,

Vamsi