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:
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 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.
Any help would be greatly appreciated.
sha.[No_] AS [OrderNumber]
, sha.[Order Date] AS [OrderDate]
, sha.[Requested Delivery Date] AS [RequestedDeliveryDate]
, sha.[Promised Delivery Date] AS [PromisedDeliveryDate]
, sla.[Version No_] AS [OrderVersion]
, sla.[No_] AS [ItemNumber]
, sla.[Quantity] AS [OrderQuantity]
, sla.[Quantity Shipped] AS [ShippedQuantity]
[Sales Header Archive] sha
LEFT JOIN [Sales Line Archive] sla
ON sha.[No_] = sla.[Document No_]
AND sha.[Version No_] = sla.[Version No_]
sla.[Document No_] IN('S20026', 'S19856') AND
sla.[Type] = '2'
, sla.[Version No_] ASC
, sla.[No_] ASC