Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Stock Ageing Analysis Using FIFO Calculation

Hello clever people, I have been struggling to create an Ageing view of the stock on hand in my two retail stores.    I have the daily Stock on Hand (SOH) and Sales for each store by date and SKU,...
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi there.

You say "I have found many examples in SQL about how to achieve this […]". Would you please give me a link to this SQL? Also, it would be advisable that you post your model here. It can be simplified to only those tables and relationships that matter for this task. Also, you could strip the model of any columns that are just nuisance. That would definitely help.

 

Thanks.

 

PS. I can't acccess files on shared drives like Google or OneDrive from work.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Daxer,

 

Thank you for your interest in assisting, I really appreciate it!  I do not have permission on the forum to upload file (not sure why Microsoft does not allow everyone to do this)

 

The model is fairly simple:

 

 

Lookup Tables:

Dates: simple date table with dates, months, years etc

Range: product range table with SKU and Product Name

Stores: store ID and Store Name

 

Fact Tables: 

Dispatches from DC:

 

 

SOH (note that this is the closing balance each day)

 

Sales:

 

Dispatches from DC

 

Finally, I have a table with the age buckets I would like to classify the SOH into:

 

The end result would look something like this:

 

I think the SQL query that matches my requirement the closest is http://www.kodyaz.com/sap-abap/stock-aging-using-sqlscript-on-sap-hana-database.aspx

 

Cheers, 

 

Phil