Skip to Content
0
Former Member
Mar 17, 2008 at 02:20 AM

Inventory aging...query n calculation error

13 Views

I'm doing on Inventory Aging, so the goal is to see how many item was in the warehouse last month, last two months and so on, it's calculated as the end of the month, so for example:

Jan 08,

beginning quantity = 0

total good receipt in Jan 08 = 100

total good issue in Jan 08 = 75,

ending quantity = 25

Then if now was February, the Aging for the column 0-29 / M - 1 or whatever will be 25.

I found a way to do this as my previous post, but there's some problem i can't figure out why.. first, the q will not be accurate, then i check on the specific good issue and good receipt, and i realize that all quantity in the good receipt and good issue is multiplied by 4. I've check on the SQL 2005 query, and the database still ok, it's just everytime i put the query on the 'user query', the result is x4.

Here is part of the code.

SELECT T0.[ItemCode], T0.[Dscription], max(T1.[OnHand]) AS 'NOW',

/--

SUM(CASE DATEDIFF(MM,T0.[DocDate], GETDATE()) WHEN 0 THEN T0.[OutQty] ELSE 0 END)as'out',

SUM(CASE DATEDIFF(MM,T0.[DocDate], GETDATE()) WHEN 0 THEN T0.[InQty] ELSE 0 END)as'in',

--/

max(T1.[OnHand]) +

SUM(CASE DATEDIFF(MM,T0.[DocDate], GETDATE()) WHEN 0 THEN T0.[OutQty] ELSE 0 END) -

SUM(CASE DATEDIFF(MM,T0.[DocDate], GETDATE()) WHEN 0 THEN T0.[InQty] ELSE 0 END)AS 'M1',

max(T1.[OnHand]) +

SUM(CASE DATEDIFF(MM,T0.[DocDate],GETDATE()) WHEN 0 THEN T0.[OutQty] ELSE 0 END) -

SUM(CASE DATEDIFF(MM,T0.[DocDate],GETDATE()) WHEN 0 THEN T0.[InQty] ELSE 0 END) +

SUM(CASE DATEDIFF(MM,T0.[DocDate],GETDATE()) WHEN 1 THEN T0.[OutQty] ELSE 0 END) -

SUM(CASE DATEDIFF(MM,T0.[DocDate],GETDATE()) WHEN 1 THEN T0.[InQty] ELSE 0 END) AS 'M2',

FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode, OWHS T2

GROUP BY T0.[ItemCode], T0.[Dscription]

ORDER BY T0.[ItemCode]

The section marked with /-- and --/ is the code for testing the actual 'in' and 'out' of the inventory, it will not be put in the final code..

any help or comment will be appreciated.

Thanks.

Martin