Inventory Ageing for Batch item

[Former Member]

Hi,

Please let me know if any one has query for inventory ageing for Batch management item.

regards

Suresh S

[former_member196647]

Hi Suresh,

Wouldn't it be a simple query from OIBT table, there you've item code, batch number, in date for that batch, and quantity.

Should be something like this:

SELECT T0.WhsCode, T0.ItemCode, T0.ItemName, T0.BatchNum, T0.CreateDate, 
DateDiff(dd, T0.CreateDate, GetDate()) As Days, T0.Quantity
FROM OIBT T0
WHERE T0.Quantity > 0
ORDER BY T0.WhsCode, T0.ItemCode, T0.CreateDate

[Former Member]

Hi Rahul

Thanks for your reply. But i need the report like customer aging.

<45 days , 45 to 60 days, 60 to 90 days.

Can you please help in regarding this.

regards

Suresh S

[former_member196647]

What are you using to create the report SSRS or Crystal. It would be difficult to accomplish in single query.

Rahul

[Former Member]

Hi,

I am not using Crystal. Only using SQL query.

if you can incorporate the above condition then it would be fine for me.

Please incorporate the condition

<30 days, 30 to 60 days, 60 to 90 days

in the query itself.

regards

Suresh S

[former_member196647]

Use following query:

SELECT T0.WhsCode, T0.ItemCode, T0.ItemName, T0.BatchNum, T0.CreateDate, 
DateDiff(dd, T0.CreateDate, GetDate()) As Days, T0.Quantity,
case when (datediff(dd, createdate, getdate())<=30) then 'Upto 30 Days' 
	when (datediff(dd, createdate, getdate())>30 and datediff(dd, createdate, getdate())<=60) then '31 to 60 Days'
	when (datediff(dd, createdate, getdate())>60 and datediff(dd, createdate, getdate())<=90) then '61 to 90 Days'
	else 'More than 90 Days'
	end as days
FROM OIBT T0
WHERE T0.Quantity > 0
ORDER BY T0.WhsCode, T0.ItemCode, T0.CreateDate

Edited by: Rahul Jain on Jan 30, 2009 10:28 AM

[Former Member]

Thanks