Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community 🙂
I'm trying to track the progress/flow of a product inside a warehouse until it leaves it. I have these stages the product goes through:
Order picking
Packing
Verification
Sent
Completed
I have a start and end date for which of these stages, and I'd like to answer the following questions:
- What is the duration (in days) from when a document starts the "Order Picking" stage until it gets to the Packing stage?
- What is the duration (in days) from when a documents exits the Packing stage and starts the Verification stage?
and so on...
Any ideas of how this process should be modelled in the script?
Like, should I create a Product_Stage_Start_Date and Product_Stage_End_Date for each stage, left joining all to the fact table?
If somebody knows any better approach, please let me know. 🙂
Thanks,
Lisa
Hi Lisa,
I don't know what volume of data you're looking at so this might be too resource heavy for you, but what I'd do is create a fact table based on each event type
Order Picking Start
Order Picking End
Packing Start
Packing End
etc, etc
Add a 'Stage Type' field containing 'Order Picking', 'Packing', etc for easy filtering
Add a 'Start / End' flag to indicate whether it's the start or end of a stage
Optionally, if it's an End event, add a Duration field
Add a single date field relating to the date of the event
This should make it pretty simple to do your calculations
Hi @marcus_malinow ,
Thanks for your input! I'll put that solution in practice, but indeed the volume of data is huge. Is there any other approach more resource-friendly?
Lisa