subtract mutlple rows from top row value

  • Here is my sample data: order number, location, stop type, stop datetime, column i made pup =1, drp =2.

    1303927Whouse1 PUP43949032013-08-11 07:26:33.0001

    1303927Store1DRP43949042013-08-11 08:31:46.0002

    1303927Store2DRP43949072013-08-11 09:28:57.0002

    1303927Store3DRP43950402013-08-11 10:38:53.0002

    What I need to do is calculate the time between the PUP time, and each DRP time. I'm thinking a cursor but looking for suggestions on how to best accomplish this? Thanks.

  • Let's start with some ddl and actual sample data. The problem with just posting some characters is we have no idea what the datatypes are or where the column breaks are. If you instead post code to create a table and then fill that table with inserts there is no room for error. The other advantage is that you will have a lot more people willing to help.

    I have no idea if this is right because I had to make some guesses.

    if OBJECT_ID('tempdb..#Something') is not null

    drop table #Something

    create table #Something

    (

    OrderNum int,

    Location varchar(10),

    PUPDRP char(3),

    SomeValueOfSomething int,

    StopTime datetime,

    ColumnIMadeUP tinyint

    )

    insert #Something

    select 1303927, 'Whouse1', 'PUP', 4394903, '2013-08-11 07:26:33.000', 1 union all

    select 1303927, 'Store1', 'DRP', 4394904, '2013-08-11 08:31:46.000', 2 union all

    select 1303927, 'Store2', 'DRP', 4394907, '2013-08-11 09:28:57.000', 2 union all

    select 1303927, 'Store3', 'DRP', 4395040, '2013-08-11 10:38:53.000', 2

    select * from #Something

    If that is not right then please modify this and post it the format that it should be.

    If this is correct can you please explain what you mean "What I need to do is calculate the time between the PUP time, and each DRP time."

    I'm thinking a cursor but looking for suggestions on how to best accomplish this? Thanks.

    You absolutely 100% do NOT need a cursor for this. Cursors have their place but straight data manipulation is not it. They are just too slow.

    _______________________________________________________________

    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/

  • Based on what I think you may be looking for I came up with:

    WITH ddl_next_time_please(order_number, location, stop_type, stop_datetime, col) AS

    (SELECT 1303927,'Whouse1 PUP',4394903,'2013-08-11 07:26:33.000',1UNION ALL

    SELECT 1303927,'Store1 DRP',4394904,'2013-08-11 08:31:46.000', 2UNION ALL

    SELECT 1303927,'Store2 DRP',4394907,'2013-08-11 09:28:57.000', 2UNION ALL

    SELECT 1303927,'Store3 DRP',4395040,'2013-08-11 10:38:53.000', 2)

    SELECT d1.*, DATEDIFF(n,d2.stop_datetime,d1.stop_datetime) AS [pup_to_drp(minutes)]

    FROM ddl_next_time_please d1

    CROSS APPLY

    (SELECT TOP 1 stop_datetime FROM ddl_next_time_please WHERE col=1) d2

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • my apologies for the lack of code in my original post. Newbie mistake I haven't made many posts and duly noted. However what you have is exactly right.

    The first line has a PUPDRP type of "PUP". The stop time is the time the truck stopped the loading facility (PUP = Pick Up). The 3 remaining lines are designated DRP meaning "drop" so the times in the stop time there is the time that the truck stopped the drop location. I need to calculate how long it was between the time the truck loaded, and each stop.

    Time between loading and stop 1 = 1 hrs

    time between loading and stop 2 = 2 hrs

    time between loading an stop 3 = 2 hrs

    I know that every order has one pickup and potentially multiple drop offs.

    Hope that helps clarify things.

    Sean Lange (9/6/2013)


    Let's start with some ddl and actual sample data. The problem with just posting some characters is we have no idea what the datatypes are or where the column breaks are. If you instead post code to create a table and then fill that table with inserts there is no room for error. The other advantage is that you will have a lot more people willing to help.

    I have no idea if this is right because I had to make some guesses.

    if OBJECT_ID('tempdb..#Something') is not null

    drop table #Something

    create table #Something

    (

    OrderNum int,

    Location varchar(10),

    PUPDRP char(3),

    SomeValueOfSomething int,

    StopTime datetime,

    ColumnIMadeUP tinyint

    )

    insert #Something

    select 1303927, 'Whouse1', 'PUP', 4394903, '2013-08-11 07:26:33.000', 1 union all

    select 1303927, 'Store1', 'DRP', 4394904, '2013-08-11 08:31:46.000', 2 union all

    select 1303927, 'Store2', 'DRP', 4394907, '2013-08-11 09:28:57.000', 2 union all

    select 1303927, 'Store3', 'DRP', 4395040, '2013-08-11 10:38:53.000', 2

    select * from #Something

    If that is not right then please modify this and post it the format that it should be.

    If this is correct can you please explain what you mean "What I need to do is calculate the time between the PUP time, and each DRP time."

    I'm thinking a cursor but looking for suggestions on how to best accomplish this? Thanks.

    You absolutely 100% do NOT need a cursor for this. Cursors have their place but straight data manipulation is not it. They are just too slow.

  • tdanley (9/6/2013)


    my apologies for the lack of code in my original post. Newbie mistake I haven't made many posts and duly noted. However what you have is exactly right.

    The first line has a PUPDRP type of "PUP". The stop time is the time the truck stopped the loading facility (PUP = Pick Up). The 3 remaining lines are designated DRP meaning "drop" so the times in the stop time there is the time that the truck stopped the drop location. I need to calculate how long it was between the time the truck loaded, and each stop.

    Time between loading and stop 1 = 1 hrs

    time between loading and stop 2 = 2 hrs

    time between loading an stop 3 = 2 hrs

    I know that every order has one pickup and potentially multiple drop offs.

    Hope that helps clarify things.

    No problem. Everybody was new around here at one point in time. It is hard to know what to post for these types of questions.

    That does help clarify. I had a feeling you wanted that but I have been bitten so many times by coding a solution to the wrong problem. 😛

    Unless I am mistaken, the excellent code that Alan posted should do exactly that. Well except that no matter how I look at it it seems that stop #3 should be 3 hours. 7:26am - 10:38am

    If his code does not produce the desired output post back and we will figure out what we can do to help.

    _______________________________________________________________

    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/

  • I took what Sean put together and added a little more sample data (a second order number) and changed it up a little. If I am correct you need the difference between the PUP and DRP that relate to each order number. Let us know if this does is what you need.

    if OBJECT_ID('tempdb..#Something') is not null

    drop table #Something

    create table #Something

    (

    OrderNum int,

    Location varchar(10),

    PUPDRP char(3),

    SomeValueOfSomething int,

    StopTime datetime,

    col tinyint

    )

    insert #Something

    select 1303927, 'Whouse1', 'PUP', 4394903, '2013-08-11 07:26:33.000', 1 union all

    select 1303927, 'Store1', 'DRP', 4394904, '2013-08-11 08:31:46.000', 2 union all

    select 1303927, 'Store2', 'DRP', 4394907, '2013-08-11 09:28:57.000', 2 union all

    select 1303927, 'Store3', 'DRP', 4395040, '2013-08-11 10:38:53.000', 2 union all

    select 1303928, 'Whouse1', 'PUP', 4394903, '2013-08-12 07:26:33.000', 1 union all

    select 1303928, 'Store1', 'DRP', 4394904, '2013-08-12 09:31:46.000', 2 union all

    select 1303928, 'Store2', 'DRP', 4394907, '2013-08-12 11:28:57.000', 2 union all

    select 1303928, 'Store3', 'DRP', 4395040, '2013-08-12 12:38:53.000', 2

    select d1.*, DATEDIFF(HOUR,d2.StopTime,d1.StopTime) AS TimeBetween

    from #Something d1

    JOIN (SELECT OrderNum, StopTime FROM #Something WHERE col=1) d2

    ON d1.OrderNum=d2.OrderNum

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 6 posts - 1 through 5 (of 5 total)

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