RE: Get original order quantities and dates, and find any changes or additions to quantities in subsequent order version(s) – SQLServerCentral

J Livingston SQL

SSC Guru

Points: 51550

April 16, 2014 at 2:26 pm

Sean

the following works,,,just left out the "QuantityChange" column (don't think its relevant to the question)

CREATE TABLE Orders

(

SalesOrderNumberNVARCHAR(20)

,VersionNumberINT

,ItemNumberNVARCHAR(20)

,OrderDateDATETIME

,RequestedDeliveryDateDATETIME

,PromisedDeliveryDateDATETIME

,QuantityOrderedDECIMAL(38,18)

,QuantityChangeDECIMAL(38,18)

,PostingDateDATETIME

,QuantityShippedDECIMAL(38,18)

,LocationCodeNVARCHAR(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')

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day