Post reply

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

  • April 16, 2014 at 1:46 pm

    #306493

    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:

    SalesOrderNumberItemNumber OrderDateRequestedDeliveryDatePromisedDelivery DateQuantityOrderedPostingDateQuantityShippedLocationCode

    S1985610568 2/13/20143/14/20143/14/201422000

    S1985612309 2/13/20143/14/20143/14/201410000

    Then Item 10526 gets added in version 3 < this line is just to visually show:

    SalesOrderNumberItemNumberOrderDateRequestedDeliveryDatePromisedDelivery DateQuantityOrderedPostingDateQuantityShippedLocationCode

    S19856105262/13/20143/10/20143/10/20147650

    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 ItemNumberOrderDateRequestedDeliveryDatePromisedDelivery DateQuantityOrderedPostingDate QuantityShippedLocationCode

    S19856 105682/13/20143/10/20143/10/2014220003/9/2014 2200020

    S19856 123092/13/20143/10/20143/10/2014100003/9/2014 1000020

    S19856 105262/13/20143/10/20143/10/201476503/9/2014 765020

    The desired Output for this Order should be:

    SalesOrderNumber ItemNumber OrderDate RequestedDeliveryDate PromisedDeliveryDate QuantityOrdered QuantityChange 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

    SalesOrderNumber ItemNumberOrderDateRequestedDeliveryDatePromisedDelivery DateQuantityOrderedQuantityChangedPostingDateQuantityShippedLocationCode

    S20026 105682/21/20143/14/20143/14/201440000

    S20026 105682/21/20143/14/20143/14/20144000023000

    S20026 105682/21/20143/14/20143/14/20142300024450

    S20026 105682/21/20143/14/20143/14/2014244503/14/20142445020

    This is a script to create an Orders table using the data described in the examples:

    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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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, QuantityChange, 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')

  • April 16, 2014 at 2:02 pm

    #1706580

    Your inserts do not work. There are more columns specified than values provided.

    Also, what are you looking for as output based on this sample data?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • April 16, 2014 at 2:26 pm

    #1706586

    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

  • April 16, 2014 at 2:48 pm

    #1706594

    J Livingston SQL (4/16/2014)

    Sean

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

    Cool. Now if we can find out what the question is we can provide an answer. 😛

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • April 16, 2014 at 3:05 pm

    #1706599

    I made a comment that reads "the desired output"

    That is what the output after processing the orders, respectively s19856 and s20026 should look like.

    The column QuantityChanged should show to what value the (original) QuanitityOrdered changed to.

    I sincerely apologize if I was not more precise.

    Thank you for your consideration.

  • April 16, 2014 at 3:08 pm

    #1706600

    itortu (4/16/2014)

    I made a comment that reads "the desired output"

    That is what the output after processing the orders, respectively s19856 and s20026 should look like.

    The column QuantityChanged should show to what value the (original) QuanitityOrdered changed to.

    I sincerely apologize if I was not more precise.

    Thank you for your consideration.

    could you repost the insert scripts so that they work properly....it may help if we know the quantity has changed

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

  • April 16, 2014 at 3:10 pm

    #1706604

    sidebar...js this a MS NAV database?

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

  • April 16, 2014 at 8:10 pm

    #1706632

    here is the updated script.

    sorry for the delay.

    yes, it is a ms NAV database.

    thank you 😀

    CREATE TABLE Orders

    (

    SalesOrderNumberNVARCHAR(20)

    ,VersionNumberINT

    ,ItemNumberNVARCHAR(20)

    ,OrderDateDATETIME

    ,RequestedDeliveryDateDATETIME

    ,PromisedDeliveryDateDATETIME

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

  • April 17, 2014 at 7:37 am

    #1706757

    I still don't understand your desired output. The formatting from this site doesn't help either. 😉 You probably need to create a temp table with your desired output so we can visually see what is going on.

    Do you want one result set with all those rows you posted? Or did you want a different result set for each version?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • April 17, 2014 at 8:09 am

    #1706773

    I have attached an excel file using the two orders I used in the sql scrip, those are s19856 and s20026

    on sheet 1 is the data as in the table, and on sheet 2 the desire output from these two orders

    i hope this offers more visibility to what i need to have.

    thank you very much for your response and willingness to help.

  • April 17, 2014 at 8:46 am

    #1706795

    OK. I need to understand some of the business rules here. Let's start with the first order and the first item on it.

    select *

    from Orders

    where SalesOrderNumber = 'S19856'

    and ItemNumber = 10568

    I see that we are turning 5 rows into 1. Why do we use the dates from version 1 and the Posting Date from version (4 or 5?). Do you really just want aggregate data? Like MAX(OrderDate), Max(RequestedDeliveryDate), SUM(QuantityShipped)

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • April 17, 2014 at 8:57 am

    #1706800

    I see that we are turning 5 rows into 1. Why do we use the dates from version 1 and the Posting Date from version (4 or 5?). Do you really just want aggregate data? Like MAX(OrderDate), Max(RequestedDeliveryDate), SUM(QuantityShipped)

    Our ERP system creates new versions of an Order not only when a Date filed or Quantity field are changed.

    A user could have entered a comment, or modified any other field that I am not interested in for this report

    Those changes can make the system create a new version of the Order too.

    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 the Quantity Shipped column and the Posting Date. Only the last version of an Order hold value on those columns.

    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.

    I hope this is clear. If not let me know I can try to be more precise, and brief.

  • April 17, 2014 at 11:41 am

    #1706881

    is the "quantity changed" column actually recorded in the Orders table?

    it doesn't appear in your test setup

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

  • April 17, 2014 at 11:51 am

    #1706885

    I am concerned that your sample data may be oversimplified...have you considered and provided test data for ALL possibilities.....I am think along the lines of:

    >item being deleted from order

    >order lines being shipped/posted on different days

    >change to location code

    >quantity ordered <> quantity shipped

    probably others that you can think of .....

    probably best for all us to get all of the permutations explained first off......otherwise this could turn into a lengthy process 🙂

    regards JLS

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

  • April 17, 2014 at 12:36 pm

    #1706898

    no that column is just a placeholder for the quantity that changes in other versions.

Viewing 15 posts - 1 through 15 (of 45 total)

You must be logged in to reply to this topic. Login to reply