Modelling different start/end dates of stages

[valpassos]

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

[marcus_malinow]

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 

[valpassos]

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