Solved

show original order quantity, quantity changes, last quantity

Posted on 2014-04-14
18
323 Views
Last Modified: 2014-04-16
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
0
Comment
Question by:metropia
  • 11
  • 4
  • 2
  • +1
18 Comments
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 50 total points
Comment Utility
It's not very clear what your logic is for selecting the Quantity Ordered so if you can clarify that I'll modify the query below accordingly.

WITH cteOrderVersion
AS
(
	SELECT Header.[No_]						AS OrderNo,
		Header.[Order Date]					AS OrderDate,
		Header.[Requested Delivery Date]	AS RequestedDeliveryDate,
		Header.[Promised Delivery Date]		AS PromisedDeliveryDate,
		Detail.[Version No_]				AS OrderVersion,
		Detail.[No_]						AS ItemNumber,
		Detail.[Quantity]					AS OrderQuantity,
		Detail.[Quantity Shipped]			AS ShippedQuantity,
		ROW_NUMBER() OVER (PARTITION BY Header.[No_] ORDER BY Header.[Version No_]) AS VersionNumber,
		COUNT(Header.[Version No_] AS VersionCount
	FROM [Sales Header Archive] AS Header
	LEFT OUTER JOIN [Sales Line Archive] AS Detail
		ON Header.[No_] = Detail.[Document No_]
		AND Header.[Version No_] = Detail.[Version No_]
)
SELECT Version1.OrderNo,
	Version1.OrderDate,
	Version1.RequestedDeliveryDate,
	Version1.PromisedDeliveryDate,
	??? AS OrderQuantity,
	VersionLast.ShippedQuantity
FROM cteOrderVersion AS Version1
LEFT OUTER JOIN cteOrderVersion AS VersonLast
	ON Version1.OrderNo = VersionLast.OrderNo
	AND Version1.VersionNumber = 1
	AND VersionLast.VersionNumber = VersionLast.VersionCount

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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.
0
 

Author Comment

by:metropia
Comment Utility
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)
0
 

Author Comment

by:metropia
Comment Utility
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

0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 300 total points
Comment Utility
Maybe something like this?!:

SELECT
    o_v1.OrderNumber,
    o_v1.OrderDate,
    o_v1.RequestedDeliveryDate,
    o_v1.PromisedDeliveryDate,
    o_v1.OrderQuantity,
    o_vn.OrderVersion,
    o_vn.OrderQuantity,
    o_vn.ShippedQuantity    
FROM Orders o_v1
OUTER APPLY (
    SELECT o_vn_.OrderVersion, o_vn_.OrderQuantity, o_vn_.ShippedQuantity
    FROM Orders o_vn_
    WHERE
        o_vn_.OrderNumber = o_v1.OrderNumber AND
        (o_vn_.OrderVersion = 1 OR o_vn_.OrderQuantity <> o_v1.OrderQuantity)
) AS o_vn
WHERE
    o_v1.OrderVersion = 1
    --AND o_v1.OrderNumber = 'S20026'
ORDER BY
    o_v1.OrderNumber, o_vn.OrderVersion
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 150 total points
Comment Utility
Hi,

select
	oo.OrderNumber
	, oo.OrderDate
	, oo.RequestedDeliveryDate
	, oo.PromisedDeliveryDate
	, oo.ItemNumber
	, oo.OrderVersion
	, oo.OrderQuantity
	, ll.OrderVersion
	, ll.ShippedQuantity
from (
	select 
		oi.OrderNumber
		, oi.ItemNumber
		, min( oi.OrderVersion ) FirstVersionNumber
		, max( oi.OrderVersion ) LastVersionNumber
	from dbo.Orders oi
	group by
		oi.OrderNumber
		, oi.ItemNumber
	) mm
inner join dbo.Orders oo
	on oo.OrderNumber = mm.OrderNumber
	and oo.ItemNumber = mm.ItemNumber
	and oo.OrderVersion = mm.FirstVersionNumber
inner join dbo.Orders ll
	on ll.OrderNumber = mm.OrderNumber
	and ll.ItemNumber = mm.ItemNumber
	and ll.OrderVersion = mm.LastVersionNumber
;

Open in new window


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.

HTH
  David
0
 

Author Comment

by:metropia
Comment Utility
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.
0
 

Author Comment

by:metropia
Comment Utility
Hi David,

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

Expert Comment

by:David Todd
Comment Utility
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
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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.
0
 

Author Comment

by:metropia
Comment Utility
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

0
 

Author Comment

by:metropia
Comment Utility
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
0
 

Author Comment

by:metropia
Comment Utility
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
0
 

Author Comment

by:metropia
Comment Utility
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
0
 

Author Comment

by:metropia
Comment Utility
I do not need to pivot the output of the dataset. The stored procedure will be feeding a SSRS report once it is completed :/
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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.
0
 

Author Comment

by:metropia
Comment Utility
I apologize for causing frustration Scott.
I honestly did not realized I was changing the question very much.
0
 

Author Closing Comment

by:metropia
Comment Utility
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.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now