Get original order qty and dates, and find any changes or additions to qty in subsequent order versions
I am due to work on this problem, but I have a lot of trouble trying to figure out how to do it on the technical side of it.
Any help would be appreciated.
I try to provide all the details I have so far, and data, if that helps in finding the answer.
This is the Order explanation:
First Version is always = 1
Last Version will have Shipped Quantities <> 0
1. I would need to show the Order's 1st version OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, LocationCode
2. I would need to show any changes in Quantity Ordered. Those changes can occur in any version of the Order and for any Item. Also a different Item can be added to the Order in any of its' versions, for that newly added Item I need to grab: OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, LocationCode
3. I would need to show the Last version Shipped Quantity for each Order's Item.
Example using data attached and Order Number S19856 this order in its 1st version the Order has two Items 10568 and 12309
I would show from version 1:
Then Item 10526 gets added in version 3 < this line is just to visually show:
Nothing changes in version 4 (regarding quantity and date fields)
Version 5 has the Quantity Shipped populated with what actually got shipped.
Notice how RequestedDeliveryDate and PromisedDeliveryDate for the two original Items got changed to the same datas for the item added on version 3:
The desired Output for this Order should be:
Example using Order S20026 this order in its version #1 has one Item 10568
1.	First Version I need to retrieve this version Quantity Ordered of 40,000 and OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered
2.	Version 2 through 3 nothing changes.
2.	In Version 4 Quantity Ordered changed to 23,000. I need to get this into column Quantity Changed and leave 40,000 in OrderedQuanity.
3.	In Version 5 Quantity Ordered remained on 23,000. I would make no changes in record to retrieve.
4. 	In Version 6 Quantity Ordered changed to 24,4500. I need to get this into column Quantity Changed and leave 40, 000
Last version I would grab Quantity Shipped and Posting Date
I am not sure how to approach to this problem, I know I need a stored procedure, but how to iterate through the records is challenging.
For your consideration and any help thank you very much.
create-insert-script.sql
Any help would be appreciated.
I try to provide all the details I have so far, and data, if that helps in finding the answer.
This is the Order explanation:
First Version is always = 1
Last Version will have Shipped Quantities <> 0
1. I would need to show the Order's 1st version OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, LocationCode
2. I would need to show any changes in Quantity Ordered. Those changes can occur in any version of the Order and for any Item. Also a different Item can be added to the Order in any of its' versions, for that newly added Item I need to grab: OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, LocationCode
3. I would need to show the Last version Shipped Quantity for each Order's Item.
Example using data attached and Order Number S19856 this order in its 1st version the Order has two Items 10568 and 12309
I would show from version 1:
SalesOrderNumber	ItemNumber	OrderDate	RequestedDeliveryDate	PromisedDelivery Date	QuantityOrdered	PostingDate	QuantityShipped	LocationCode
S19856				10568		2/13/2014	3/14/2014				3/14/2014				22000										
S19856				12309		2/13/2014	3/14/2014				3/14/2014				10000Then Item 10526 gets added in version 3 < this line is just to visually show:
SalesOrderNumber	ItemNumber	OrderDate	RequestedDeliveryDate	PromisedDelivery Date	QuantityOrdered	PostingDate	QuantityShipped	LocationCode
S19856				10526		2/13/2014	3/10/2014				3/10/2014				7650	Nothing changes in version 4 (regarding quantity and date fields)
Version 5 has the Quantity Shipped populated with what actually got shipped.
Notice how RequestedDeliveryDate and PromisedDeliveryDate for the two original Items got changed to the same datas for the item added on version 3:
SalesOrderNumber	ItemNumber	OrderDate	RequestedDeliveryDate	PromisedDelivery Date	QuantityOrdered	PostingDate	QuantityShipped	LocationCode
S19856				10568		2/13/2014	3/10/2014				3/10/2014				22000			3/9/2014	22000			20
S19856				12309		2/13/2014	3/10/2014				3/10/2014				10000			3/9/2014	10000			20
S19856				10526		2/13/2014	3/10/2014				3/10/2014				7650			3/9/2014	7650			20The desired Output for this Order should be:
SalesOrderNumber	ItemNumber	OrderDate	RequestedDeliveryDate	PromisedDelivery Date	QuantityOrdered		Quantity Change	PostingDate	QuantityShipped	LocationCode
S19856				10568		2/13/2014	3/14/2014				3/14/2014				22000								3/9/2014	22000			20							
S19856				12309		2/13/2014	3/14/2014				3/14/2014				10000								3/9/2014	10000			20							
S19856				10526		2/13/2014	3/10/2014				3/10/2014				7650								3/9/2014	7650			20 
Example using Order S20026 this order in its version #1 has one Item 10568
1.	First Version I need to retrieve this version Quantity Ordered of 40,000 and OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered
2.	Version 2 through 3 nothing changes.
2.	In Version 4 Quantity Ordered changed to 23,000. I need to get this into column Quantity Changed and leave 40,000 in OrderedQuanity.
3.	In Version 5 Quantity Ordered remained on 23,000. I would make no changes in record to retrieve.
4. 	In Version 6 Quantity Ordered changed to 24,4500. I need to get this into column Quantity Changed and leave 40, 000
Last version I would grab Quantity Shipped and Posting Date
SalesOrderNumber	ItemNumber	OrderDate	RequestedDeliveryDate	PromisedDelivery Date	QuantityOrdered	QuantityChanged	PostingDate	QuantityShipped	LocationCode
S20026				10568		2/21/2014	3/14/2014				3/14/2014				40000				
S20026				10568		2/21/2014	3/14/2014				3/14/2014				40000			23000			
S20026				10568		2/21/2014	3/14/2014				3/14/2014				23000			24450			
S20026				10568		2/21/2014	3/14/2014				3/14/2014				24450							3/14/2014	24450			20
I am not sure how to approach to this problem, I know I need a stored procedure, but how to iterate through the records is challenging.
For your consideration and any help thank you very much.
create-insert-script.sql
ASKER
I need to update the script, there was an extra column in it:
CREATE TABLE Orders
(
SalesOrderNumber NVARCHAR(20)
, VersionNumber INT
, ItemNumber NVARCHAR(20)
, OrderDate DATETIME
, RequestedDeliveryDate DATETIME
, PromisedDeliveryDate DATETIME
, QuantityOrdered DECIMAL(38,18)
, PostingDate DATETIME
, QuantityShipped DECIMAL(38,18)
, LocationCode NVARCHAR(10)
)
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S19856', 1, '10568', '2014-02-13 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', 22000.00000000000000000000, '1753-01-01 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S19856', 1, '12309', '2014-02-13 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', 10000.00000000000000000000, '1753-01-01 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S19856', 2, '10568', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', 22000.00000000000000000000, '1753-01-01 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S19856', 2, '12309', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', 10000.00000000000000000000, '1753-01-01 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S19856', 3, '10568', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', 22000.00000000000000000000, '1753-01-01 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S19856', 3, '12309', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', 10000.00000000000000000000, '1753-01-01 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S19856', 3, '10526', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', 7650.00000000000000000000, '1753-01-01 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S19856', 4, '10568', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', 22000.00000000000000000000, '2014-03-09 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S19856', 4, '12309', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', 10000.00000000000000000000, '2014-03-09 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S19856', 4, '10526', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', 7650.00000000000000000000, '2014-03-09 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S19856', 5, '10568', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', 22000.00000000000000000000, '2014-03-09 00:00:00.000', 22000.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S19856', 5, '12309', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', 10000.00000000000000000000, '2014-03-09 00:00:00.000', 10000.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S19856', 5, '10526', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', 7650.00000000000000000000, '2014-03-09 00:00:00.000', 7650.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S20026', 1, '10568', '2014-02-21 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', 40000.00000000000000000000, '1753-01-01 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S20026', 2, '10568', '2014-02-21 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', 40000.00000000000000000000, '1753-01-01 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S20026', 3, '10568', '2014-02-21 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', 40000.00000000000000000000, '1753-01-01 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S20026', 4, '10568', '2014-02-21 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', 23000.00000000000000000000, '1753-01-01 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S20026', 5, '10568', '2014-02-21 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', 23000.00000000000000000000, '1753-01-01 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S20026', 6, '10568', '2014-02-21 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', 24450.00000000000000000000, '2014-03-14 00:00:00.000', 0.00000000000000000000, '20')
INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate, PromisedDeliveryDate, QuantityOrdered, PostingDate, QuantityShipped, LocationCode)
VALUES ('S20026', 7, '10568', '2014-02-21 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', 24450.00000000000000000000, '2014-03-14 00:00:00.000', 24450.00000000000000000000, '20')ASKER
i have attached a mock up of what the output should look like. hope that helps
Version 1 holds the original Order Date, Requested Delivery Date, Promised Delivery Date, and Quantity Ordered. I need those.
From any other version(s) I just want to show when the Quantity changes. For Item 10568 that does not happen in any version.
The way to identify the last version of an order is by looking into a flag named Invoice = 1. Only the last version of an Order will have that values set to 1.
There can be Orders that in the version 1 contain N number of Items, and in subsequent versions new Items can get added to the Order. For those Items that get added in subsequent versions, I need to show the Order Date, Requested Delivery Date, Promised Delivery Date, and Quantity Ordered as if this is their version 1, and leave the original Items and their date and quantity columns as they were from their version 1.
There can be Orders that can start with 5 Items in their first version, but in a different version an Item got deleted. I need to show for this.
Location Code can be removed from the result set. Not need to show it anymore.
I updated the mock-up file, and got rid of the Quantity Changed column. Show all quantity changes in the Quantity column. See file attached.
mockup.xlsx
Version 1 holds the original Order Date, Requested Delivery Date, Promised Delivery Date, and Quantity Ordered. I need those.
From any other version(s) I just want to show when the Quantity changes. For Item 10568 that does not happen in any version.
The way to identify the last version of an order is by looking into a flag named Invoice = 1. Only the last version of an Order will have that values set to 1.
There can be Orders that in the version 1 contain N number of Items, and in subsequent versions new Items can get added to the Order. For those Items that get added in subsequent versions, I need to show the Order Date, Requested Delivery Date, Promised Delivery Date, and Quantity Ordered as if this is their version 1, and leave the original Items and their date and quantity columns as they were from their version 1.
There can be Orders that can start with 5 Items in their first version, but in a different version an Item got deleted. I need to show for this.
Location Code can be removed from the result set. Not need to show it anymore.
I updated the mock-up file, and got rid of the Quantity Changed column. Show all quantity changes in the Quantity column. See file attached.
mockup.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sharath, your results are awesome!
I have a question thought, well is more like an issue that I just realized. I will have to calculate the fill rate of an order.
let's say for order S20026, its original quantity was 40,000, but the actual shipped quantity was 24450, the fill rate would be 61 percent
SELECT ISNULL(24450 * 1.0 / NULLIF(40000, 0), 0) * 100
My question/ issue is how can I, with the code you provided, make this calculation using the original quantity ordered from version 1 of the order and the shipped quantity from the last version?
Thank you kindly.
I have a question thought, well is more like an issue that I just realized. I will have to calculate the fill rate of an order.
let's say for order S20026, its original quantity was 40,000, but the actual shipped quantity was 24450, the fill rate would be 61 percent
SELECT ISNULL(24450 * 1.0 / NULLIF(40000, 0), 0) * 100
My question/ issue is how can I, with the code you provided, make this calculation using the original quantity ordered from version 1 of the order and the shipped quantity from the last version?
Thank you kindly.
Are you still looking for assistance here?
ASKER
Hi Sharath,
I do still need help but I broke the question in another part.
Almost there though.
In this new question you can see what I am still missing:
Q_28419385
I hope you can help me with this, I posted all sample data, and scripts.
Thank you much.
I do still need help but I broke the question in another part.
Almost there though.
In this new question you can see what I am still missing:
Q_28419385
I hope you can help me with this, I posted all sample data, and scripts.
Thank you much.
You can use that to start and see also the documentation in SS's Books Online on CTEs.