show original order quantity, quantity changes, last quantity

I am looking to pull orders from an archive table.
in the archive table an order can have multiple revisions.

I need to be able to retrieve some fields from order version 1, some fields from the last version, and some fields from version between first and last.

Example would be:

From order version 1:
Order Date, Requested Delivery Date, Promised Delivery Date, Quantity

From versions in between:
Quantity

From last version:
Posting Date, Quantity Shipped

The complexity i think resides in the quantity column.

A data example:

I have an order 20026, it has a total of 7 versions.

version 1 has a quantity 40,000
version 2 has a quantity 40,000
version 3 has a quantity 40,000
version 4 has a quantity 23,000
version 5 has a quantity 23,000
version 6 has a quantity 24,450
version 7 has a quantity 24,450, and quantity shipped 24,450 < this is the actual qty shipped.

The query ideally needs to return

Order Number
Order Date (v1)
Requested Delivery Date (v1)
Promised Delivery Date (v1)
Quantity Ordered (v1)
(any version quantity that is different from the original v1)
Quantity Shipped (version 7 - last)

I have a starter query and some sample data attached using the order number I mention above and a second order too.

Every version of the order has a quantity column, a version column, and a quantity shipped column.

Some orders will be updated before its completion. A customer could call and say I want more that what i originally ordered, in that event, the quantity would be updated, and another version of the order would be stored in the order archive table (updated to quantities is not the only event that created a new version of the order, a user could enter some comments, edit a typo and that would also create a new version of an order)

The Quantity Shipped column will always be 0, only the last version of the order (MAX) would have the quantity shipped column populated.

The columns would display as

Quantity | Quantity Changes (display even if quantity is the same as before | quantity shipped (from the last version of the order)

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.

I haven't got the column that shows the intermediate quantity changes. Without a lot of messy code I can't see how to do this in one column. Have to do something like csv list. I think it will be messy and slow.

There are more columns than you asked for, but on first inspection it seems to do the job. It doesn't assume that the first version of the order is complete, and correctly shows that item 10526 started on OrderVersion 3 of S19856.

Question: What about partial shipments? That is, figuratively speaking, order S20026 ships around half the requested quantity on version 7 (say to stock being on back-order). But the stock arrives and version 8 ships the remaining 15550 units. Just a thought.

It's difficult to do what you want when it keeps changing. The initial q stated:
"
The query ideally needs to return
Order Number
Order Date (v1)
Requested Delivery Date (v1)
Promised Delivery Date (v1)
Quantity Ordered (v1)(any version quantity that is different from the original v1)
"

Now it sounds like you want any version quantity that is different from the previous version.

And item numbers, which are not mentioned above.

It's very frustrating to waste so much time working on something you asked for but don't want.

Featured Post

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.