Inventory aging...query n calculation error

[Former Member]

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

[Former Member]

Hi Martin,

I have created the inventory report trough XL Reporter instead of query.

the report display:

Beginning Balance Qty, Qty In, Qty Out, Ending Balance Qty.

Beginning Balance Cost, Cost In, Cost Out, Ending Balance Cost.

The data display for each warehouse and based on the Period Parameter which is selected by user.

Have you been using the XL Reporter?

If would like to see my report, send me your email address and i will send directly to you.

Regards,

Lina Yuanita

[Former Member]

Hi Lina,

Thanks for the respond. I don't have any experience in XLR, and that's why my senior asked me to do in query.

But it's ok, I will do XLR sooner or later.

my email is jm_xyz_786@yahoo.com

Thank you

Regards,

Martin

[Former Member]

Hi Martin,

I just send you the file.

Please take a look and see if it works for you.

If you have any questions about the report, dont hesitate to ask me.

Regards,

Lina Yuanita

[Former Member]

Hi Lina,

I'm trying to generate inventory aging report ,but using queries we are not able to reach the final level.Its found that you are using xlreporter for this purpose.

can you please help us in using the xl reporter for generating this report.we are new to xlreporter.

My id is rajeshnett at gmail com

or smithajac at gmail com

Thanks,

Rajesh

[Former Member]

Hi Lina!

I would be appreciated if you could send me that .ixr file too, my client requests from me the same as inventory aging reports and have no idea how to give. But thanks for this wonderful forum we can serve our clients!!

My emailadd:

retroflexus dot freemail.hu

Thank you in advance!!!

Eszter

[Former Member]

Hi Lina,

Can I ask you to send me the report as well. I would appreciate it, I have been struggling with this for quite some time and hopefully you can assist with solution.

my e.mail: wynandcil at gmail dot com

Thanks,

Wynand.