Matching the initial schedule date with an order

[MWarner]

Hello,

I am currently trying to develop a WF that calculates actual lead times of orders (time it takes for an order to be placed, shipped and arrive to its needed location). Then I need to calculate metrics based on the actual lead time vs the estimated time the suppliers said the order would take.

Orders can be one or many rows. Each week, the quantities of orders are updated, if there is a change, it indicates some of the order has shipped.

Example data: 

Week	Order	QTY	Scheduled_Ship_Date
1	A	10	Date1
1	A	10	Date2
1	B	5	Date1
2	A	5	Date1
2	A	10	Date2
2	B	5	Date1

This indicates that 5 have shipped from Order A between week 1 and 2.

I have been able to calculate the amounts that ship week by week. What I need to do next is calculate metrics. One of the metrics involves calculating the number of days between the initial scheduled shipment date and when the order shipped. The issue is that the scheduled ship date can change from one week to a next. Additionally, order lines can be broken apart from one week to the next, so an order that is initially 1 row of QTY 10 can be 2 rows of QTY 5 and 5 the next week. 

What I've done is taken an initial "snapshot" of all new orders with their original shipment dates. I'd like to compare and correctly append that snapshot to the orders that have shipped.

Ex:

Snapshot

Week	Order	QTY	Scheduled_Ship_Date
1	A	10	Date1
1	B	50	Date2

Shipment record

Week	Order	QTY	QTY_SHIPPED	Scheduled_Ship_Date
1	A	5	5	Date1
1	B	50	0	Date2
2	A	3	0	Date1
2	A	2	0	Date3
2	B	45	5	Date4

Order A was split into two lines between weeks 1 and 2, and a new shipment date for one line was changed.

What id like to create is something like the following:

Week	Order	QTY	QTY_SHIPPED	Scheduled_Ship_Date	Initial_Schdule_Ship_Date
1	A	5	5	Date1	Date1
1	B	50	0	Date2	Date2
2	A	3	0	Date1	Date1
2	A	2	0	Date3	Date1
2	B	45	5	Date4	Date2

How could I go about doing this? These tables are simplified versions of the data that I'm using but I can provide more clarification if needed. Thank you.

[geraldo]

@MWarner 

I tried to understand the steps you put and the examples but I couldn't understand.
if it could improve the need.