Link to home
Start Free TrialLog in
Avatar of metropia
metropiaFlag for United States of America

asked on

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.

Any help would be greatly appreciated.

SELECT DISTINCT
	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]
FROM 
[Sales Header Archive] sha 
LEFT JOIN [Sales Line Archive] sla
	ON sha.[No_] = sla.[Document No_] 
	AND sha.[Version No_] = sla.[Version No_]
WHERE 
	sla.[Document No_] IN('S20026', 'S19856') AND 
	sla.[Type] = '2'
ORDER BY 
	sha.[No_] ASC
,	sla.[Version No_] ASC 
,	sla.[No_] ASC

Open in new window

sample-data-order-archive.txt
SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I need the sample data in CREATE TABLE and INSERT statements.  It takes me too long to convert/prep the data, usu. much longer than it takes me to actually write the query code.
Avatar of metropia

ASKER

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)
CREATE TABLE Orders
(
	[OrderNumber] NVARCHAR(10)
,	[OrderDate]	DATETIME
,	[RequestedDeliveryDate] DATETIME
,	[PromisedDeliveryDate] DATETIME
,	[OrderVersion] NVARCHAR(10)
,	[ItemNumber] NVARCHAR(20)
,	[OrderQuantity] DECIMAL(38, 18)
,	[ShippedQuantity] DECIMAL(38, 18)
)

INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S19856', '2014-02-13 00:00:00.000', '2014-03-14 00:00:00.000',	'2014-03-14 00:00:00.000',	'1', '10568', 22000.00000000000000000000, 0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S19856', '2014-02-13 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', '1', '12309', 10000.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S19856', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', '2', '10568', 22000.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S19856', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', '2', '12309', 10000.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S19856', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', '3', '10526',  7650.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S19856', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', '3', '10568', 22000.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S19856', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', '3', '12309', 10000.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S19856', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', '4', '10526',  7650.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S19856', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', '4', '10568', 22000.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S19856', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', '4', '12309', 10000.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S19856', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', '5', '10526',  7650.00000000000000000000,  7650.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S19856', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', '5', '10568', 22000.00000000000000000000, 22000.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S19856', '2014-02-13 00:00:00.000', '2014-03-10 00:00:00.000', '2014-03-10 00:00:00.000', '5', '12309', 10000.00000000000000000000, 10000.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S20026', '2014-02-21 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', '1', '10568', 40000.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S20026', '2014-02-21 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', '2', '10568', 40000.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S20026', '2014-02-21 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', '3', '10568', 40000.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S20026', '2014-02-21 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', '4', '10568', 23000.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S20026', '2014-02-21 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', '5', '10568', 23000.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S20026', '2014-02-21 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', '6', '10568', 24450.00000000000000000000,     0.00000000000000000000)
INSERT INTO Orders ([OrderNumber], [OrderDate],	[RequestedDeliveryDate], [PromisedDeliveryDate], [OrderVersion], ItemNumber], [OrderQuantity], [ShippedQuantity])
VALUES ('S20026', '2014-02-21 00:00:00.000', '2014-03-14 00:00:00.000', '2014-03-14 00:00:00.000', '7', '10568', 24450.00000000000000000000, 24450.00000000000000000000)

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I created a stored procedure with 3 variable tables: salesorders_version_first, sales_orders_version_last, salesorders_quantity_changes

BEGIN
	
	DECLARE @SalesOrders_Version_First TABLE
	(				
		[OrderNumber]					NVARCHAR(10)
	,	[VersionNumber]					NVARCHAR(10)
	,	[ItemNumber]					NVARCHAR(20)
	,	[OrderDate]						DATETIME
	,	[RequestedDeliveryDate]			DATETIME
	,	[LeadTimeReqDelivery Date]		INT
	,	[PromisedDeliveryDate]			DATETIME
	,	[LeadTimePromisedDeliveryDate]	INT
	,	[OrderQuantity]					DECIMAL(38,18)
	,	[LocationCode]					NVARCHAR(10)
	)
	INSERT INTO @SalesOrders_Version_First
	(
		[OrderNumber]
	,	[VersionNumber]
	,	[ItemNumber]
	,	[OrderDate]
	,	[RequestedDeliveryDate]
	,	[LeadTimeReqDelivery Date]
	,	[PromisedDeliveryDate]
	,	[LeadTimePromisedDeliveryDate]
	,	[OrderQuantity]
	,	[LocationCode]
	)		
	SELECT DISTINCT
		sha.No_							AS [OrderNumber]
	,	sha.[Version No_]				AS [VersionNumber]
	,	sla.[No_]						AS [ItemNumber]
	,	MAX(sha.[Order Date])				AS [OrderDate]
	,	MAX(sha.[Requested Delivery Date])	AS [RequestedDeliveryDate]
	,	DATEDIFF(DAY, MAX(sha.[Order Date]), MAX(sha.[Requested Delivery Date]))
											AS [LeadTimeReqDelivery Date]
	,	MAX(sha.[Promised Delivery Date])	AS [PromisedDeliveryDate]
	,	DATEDIFF(DAY, MAX(sha.[Order Date]), MAX(sha.[Promised Delivery Date]))
											AS [LeadTimePromisedDeliveryDate]
	,	SUM(sla.Quantity)					AS [QuantityOrdered]
	,	sla.[Location Code]					AS [LocationCode]
	FROM [Sales Header Archive] sha 
	LEFT OUTER JOIN [Sales Line Archive] sla 
		ON sha.[No_] = sla.[Document No_] 
		AND sha.[Version No_] = sla.[Version No_]
	WHERE sla.[Type] = 2
	AND sla.[Version No_] = 1 
	AND sha.[No_] IN ('S19856', 'S20026')
	GROUP BY
		sha.No_
	,	sha.[Version No_]
	,	sla.[No_]
	,	sla.[Location Code]	

	DECLARE @SalesOrders_Version_Last TABLE
	(				
		[OrderNumber]					NVARCHAR(10)
	,	[VersionNumber]					NVARCHAR(10)
	,	[ItemNumber]					NVARCHAR(20)
	,	[PostingDate]					DATETIME
	,	[ActualDeliveryDate]			DATETIME
	,	[LeadTimeActualDeliveryDate]	INT
	,	[ShippedQuantity]				DECIMAL(38,18)
	,	[LocationCode]					NVARCHAR(10)
	)
	INSERT INTO @SalesOrders_Version_Last
	(
		[OrderNumber]
	,	[VersionNumber]
	,	[ItemNumber]
	,	[PostingDate]
	,	[ActualDeliveryDate]
	,	[LeadTimeActualDeliveryDate]
	,	[ShippedQuantity]
	,	[LocationCode]
	)
	SELECT 
			s.[OrderNumber]
		,	s.[VersionNumber]
		,	s.[ItemNumber]
		,	s.[PostingDate]
		,	s.[ActualDeliveryDate]
		,	DATEDIFF(DAY, s.OrderDate, s.ActualDeliveryDate) AS [LeadTimeActualDeliveryDate]
		,	s.[ShippedQuantity]
		,	s.[LocationCode]
	FROM 
	(
		SELECT DISTINCT
			sha.No_							AS [OrderNumber]
		,	sha.[Version No_]				AS [VersionNumber]
		,	sla.[No_]						AS [ItemNumber]
		,	MAX(sha.[Order Date])			AS [OrderDate]
		,	MAX(sha.[Posting Date])			AS [PostingDate]
		,	DATEADD(DAY, CONVERT(INT, REPLACE(MAX(sha.[Shipping Time]), CHAR(2), '')), MAX(sha.[Posting Date]))
											AS [ActualDeliveryDate]
		,	SUM(sla.[Quantity Shipped])		AS [ShippedQuantity]
		,	sla.[Location Code]				AS [LocationCode]
		FROM [Sales Header Archive] sha
		LEFT OUTER JOIN [Sales Line Archive] sla
			ON sha.[No_] = sla.[Document No_]
			AND sha.[Version No_] = sla.[Version No_]
		WHERE sha.[Posting Date] IS NOT NULL
		AND sla.[Type] = 2 
		AND sla.[Quantity Shipped] > 0
		AND sha.[Version No_] = (SELECT MAX([Version No_]) FROM NAV.dbo.[CQC$Sales Header Archive] WHERE No_ = sha.No_)	
		AND sha.[No_] IN ('S19856', 'S20026')
		GROUP BY 
			sha.No_
		,	sha.[Version No_]
		,	sla.[No_]
		,	sla.[Location Code]	
	) s

	DECLARE @SalesOrders_Quantity_Changes TABLE
	(				
		[OrderNumber]					NVARCHAR(10)
	,	[VersionNumber]					NVARCHAR(10)
	,	[ItemNumber]					NVARCHAR(20)
	,	[OrderQuantity]					DECIMAL(38,18)
	)
	INSERT INTO @SalesOrders_Quantity_Changes
	(
		[OrderNumber]
	,	[VersionNumber]
	,	[ItemNumber]
	,	[OrderQuantity]
	)		
	SELECT DISTINCT
		sha.No_							AS [OrderNumber]
	,	sha.[Version No_]				AS [VersionNumber]
	,	sla.[No_]						AS [ItemNumber]
	,	SUM(sla.Quantity)				AS [QuantityOrdered]
	FROM [Sales Header Archive] sha 
	LEFT OUTER JOIN [Sales Line Archive] sla 
		ON sha.[No_] = sla.[Document No_] 
		AND sha.[Version No_] = sla.[Version No_]
	LEFT OUTER JOIN @SalesOrders_Version_First so_vf
		ON sha.[No_] COLLATE DATABASE_DEFAULT = so_vf.[OrderNumber]
	LEFT OUTER JOIN @SalesOrders_Version_Last so_vl
		ON sha.[No_] COLLATE DATABASE_DEFAULT = so_vl.[OrderNumber]
	WHERE sla.[Type] = 2
	AND sla.[Version No_] NOT IN (SELECT VersionNumber FROM @SalesOrders_Version_First) 
	AND sla.[Version No_] NOT IN (SELECT VersionNumber FROM @SalesOrders_Version_Last)
	AND sla.[Quantity] NOT IN (SELECT OrderQuantity  FROM @SalesOrders_Version_First)
	AND sha.[No_] IN ('S19856', 'S20026')
	GROUP BY 
		sha.No_
	,	sha.[Version No_]
	,	sla.[No_]


	SELECT * FROM @SalesOrders_Version_First
	SELECT * FROM @SalesOrders_Quantity_Changes
	SELECT * FROM @SalesOrders_Version_Last
END

Open in new window


but I am having problems in selecting the quantity changes.

if you use the sample data I attached and run a stored proc like mine, you will see the issue.
Hi David,

I like your solution but you are right, the updates in quantity value are still missing, same in my stored proc.
Hi,

Unless Scott's solution has the intermediate quantity versions - which I personally don't see as important - I think that its too much effort to add them in.

With this script I show how a function can return a csv of values. I think if you need those quantities, this is how you'll need to do it.
http://www.sqlservercentral.com/scripts/Miscellaneous/31894/

But performance isn't going to be good!

Regards
  David
I do show the intermediate quantities.  But I show each on a separate row.

There would need to be a final pivot of some sort to show them all in one row.

David, you could easily run my query on the sample data posted, including the:
  AND o_v1.OrderNumber = 'S20026'
in the WHERE and see the results.
Scotts output is:

S20026	2014-02-21 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	40000.00000000000000000000	1	40000.00000000000000000000	0.00000000000000000000
S20026	2014-02-21 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	40000.00000000000000000000	4	23000.00000000000000000000	0.00000000000000000000
S20026	2014-02-21 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	40000.00000000000000000000	5	23000.00000000000000000000	0.00000000000000000000
S20026	2014-02-21 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	40000.00000000000000000000	6	24450.00000000000000000000	0.00000000000000000000
S20026	2014-02-21 00:00:00.000	2014-03-14 00:00:00.000	2014-03-14 00:00:00.000	40000.00000000000000000000	7	24450.00000000000000000000	24450.00000000000000000000

Open in new window

Using that data as reference, ideally I would like to see:

Row of version 1,
Row of version 4 (there is the version where the quantity changes)
Row of version 7
Forgot to mention that in version 6 the quantity changed again, but version 7 has the same quantity and also turns out to be the last version so quantity shipped is not 0
is this possibly to get done using a cursor?
a way to compare the current version with the previous and use it if that version has different quantity
I do not need to pivot the output of the dataset. The stored procedure will be feeding a SSRS report once it is completed :/
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.
I apologize for causing frustration Scott.
I honestly did not realized I was changing the question very much.
I had to close this question. I did not mean to keep changing it, nor offend anyone.
I will post a new question and hope to be more clear on what I need help with.
Thank you every one for your help.