I started creating a stored procedure in which i create two variable tables, one to store all the orders where version number = 1, and type = 2 (item) which I called

DECLARE @SalesOrders_Version_First

I then created another variable table that stored orders with a column Invoice = 1, type = 2 (item) and Quantity <> 0

My issue really resides in finding the other versions of an Order in which an Item gets added, or a Quantity gets modified.

Here is my stored proc. If you can help me figuring out some of the other code, that would be very kind.

Thank you much.

AS

BEGIN

DECLARE @SalesOrders_Version_First TABLE

(

[OrderNumber]NVARCHAR(20)

,[ItemNumber]NVARCHAR(20)

,[OrderDate]DATETIME

,[RequestedDeliveryDate]DATETIME

,[PromisedDeliveryDate] DATETIME

,[OrderQuantity]DECIMAL(38,18)

,[PostingDate]DATETIME

,[ShippedQuantity]DECIMAL(38,18)

)

INSERT INTO @SalesOrders_Version_First

(

[OrderNumber]

,[VersionNumber]

,[ItemNumber]

,[OrderDate]

,[RequestedDeliveryDate]

,[PromisedDeliveryDate]

,[OrderQuantity]

,[PostingDate]

,[ShippedQuantity]

)

SELECT DISTINCT

sha.[Order Date]

,sha.[Requested Delivery Date]

,sha.[Promised Delivery Date]

,sla.Quantity

,sha.[Posting Date]

,sla.[Quantity Shipped]

FROM NAV.dbo.[Sales Header Archive] sha

LEFT OUTER JOIN NAV.dbo.[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')

--AND sha.[No_] = 'S19856'

--AND sha.[No_] = 'S20026'

--AND sha.[No_] = 'S20302'

DECLARE @SalesOrders_Version_Last TABLE

(

[OrderNumber]NVARCHAR(20)

,[VersionNumber]NVARCHAR(10)

,[ItemNumber]NVARCHAR(20)

,[OrderDate]DATETIME

,[RequestedDeliveryDate]DATETIME

,[PromisedDeliveryDate] DATETIME

,[OrderQuantity]DECIMAL(38,18)

,[PostingDate]DATETIME

,[ShippedQuantity]DECIMAL(38,18)

)

INSERT INTO @SalesOrders_Version_Last

(

[OrderNumber]

,[VersionNumber]

,[ItemNumber]

,[OrderDate]

,[RequestedDeliveryDate]

,[PromisedDeliveryDate]

,[OrderQuantity]

,[PostingDate]

,[ShippedQuantity]

)

SELECT DISTINCT

s.[OrderNumber]

,s.[VersionNumber]

,s.[ItemNumber]

,s.[OrderDate]

,s.[RequestedDeliveryDate]

,s.[PromisedDeliveryDate]

,s.[QuantityOrdered]

,s.[PostingDate]

,s.[ShippedQuantity]

FROM

(

SELECT DISTINCT

sha.No_

,sha.[Version No_]

,sla.[No_]

,sha.[Order Date]

,sha.[Requested Delivery Date]

,sha.[Promised Delivery Date]

,sla.Quantity

,sha.[Posting Date]

,sla.[Quantity Shipped]

FROM NAV.dbo.[Sales Header Archive] sha

LEFT OUTER JOIN NAV.dbo.[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.[Completely Shipped] = 1

AND sla.[Quantity Shipped] <> 0

AND sha.[No_] IN ('S19856', 'S20026')

--AND sha.[No_] = 'S19856'

--AND sha.[No_] = 'S20026'

--AND sha.[No_] = 'S20302'

) s

--DECLARE @SalesOrders_Version_Changes TABLE

--(

--[OrderNumber]NVARCHAR(20)

--,[ItemNumber]NVARCHAR(20)

--,[OrderDate]DATETIME

--,[RequestedDeliveryDate]DATETIME

--,[PromisedDeliveryDate] DATETIME

--,[OrderQuantity]DECIMAL(38,18)

--,[PostingDate]DATETIME

--,[ShippedQuantity]DECIMAL(38,18)

--)

--INSERT INTO @SalesOrders_Version_Changes

--(

--[OrderNumber]

--,[ItemNumber]

--,[OrderDate]

--,[RequestedDeliveryDate]

--,[PromisedDeliveryDate]

--,[OrderQuantity]

--,[PostingDate]

--,[ShippedQuantity]

--)

--SELECT DISTINCT

--sha.No_

--,sla.[No_]

--,sha.[Order Date]

--,sha.[Requested Delivery Date]

--,sha.[Promised Delivery Date]

--,sla.Quantity

--,sha.[Posting Date]

--,sla.[Quantity Shipped]

--FROM NAV.dbo.[CQC$Sales Header Archive] sha

--LEFT OUTER JOIN NAV.dbo.[CQC$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.[Quantity] NOT IN (SELECT OrderQuantity FROM @SalesOrders_Version_First)

--AND sla.[Version No_] NOT IN (SELECT VersionNumber FROM @SalesOrders_Version_Last)

--AND sla.[Quantity] NOT IN (SELECT ShippedQuantity FROM @SalesOrders_Version_Last)

--AND sla.[Quantity Shipped] = 0

--AND sha.[No_] IN ('S19856', 'S20026')

--AND sha.[No_] = 'S19856'

--AND sha.[No_] = 'S20026'

--AND sha.[No_] = 'S20302'

SELECT * FROM @SalesOrders_Version_First

SELECT * FROM @SalesOrders_Version_Last

--SELECT * FROM @SalesOrders_Version_Changes

END