Post reply

Calculate DateDiff between two tables and tow records

  • March 22, 2017 at 7:59 am

    #321787

    Hi all,

    I'm struggling to calculate the difference between two timestamps on two different rows and two different Columns.
    I need to substract the PickingStart from the PickingEnd by DATE_PICKING_START,  ASSOC_ID and PickingStart.

    f.e. (PickingEnd 2017-03-13 06:02:41.000)  -  (PickingStart 2017-03-13 06:02:40.000)  = 1

    This an example of what the result of DateDiff() should look like stored in Column Secs_Worked.

    ASSIGNMENTASSOC_IDDATE_PICKING_STARTPickingStartPickingEndSecs_Worked
    33928858426513/03/20172017-03-13 05:58:53.0002017-03-13 06:02:41.0001
    33928858426513/03/20172017-03-13 06:02:40.0002017-03-13 06:02:54.0002
    33928858426513/03/20172017-03-13 06:02:52.0002017-03-13 06:03:09.0000
    33928858426513/03/20172017-03-13 06:03:09.0002017-03-13 06:03:20.0002
    33928858426513/03/20172017-03-13 06:03:18.0002017-03-13 06:03:29.0000
    33928858426513/03/20172017-03-13 06:03:29.0002017-03-13 06:03:41.0000
    33928858426513/03/20172017-03-13 06:03:41.0002017-03-13 06:03:53.0001
    33928858426513/03/20172017-03-13 06:03:52.0002017-03-13 06:04:13.0000
    33928858426513/03/20172017-03-13 06:04:13.0002017-03-13 06:04:36.0002
    33928858426513/03/20172017-03-13 06:04:34.0002017-03-13 06:04:38.0000
    33928890426513/03/20172017-03-13 06:05:29.0002017-03-13 06:09:42.0001
    33928890426513/03/20172017-03-13 06:09:41.0002017-03-13 06:09:57.0000
    33928890426513/03/20172017-03-13 06:09:57.0002017-03-13 06:10:07.0000
    33928890426513/03/20172017-03-13 06:10:07.0002017-03-13 06:10:50.0001
    33928890426513/03/20172017-03-13 06:10:49.0002017-03-13 06:11:18.0001
    33928890426513/03/20172017-03-13 06:11:17.0002017-03-13 06:11:21.0000
    33928887517413/03/20172017-03-13 06:04:07.0002017-03-13 06:08:45.0001
    33928887517413/03/20172017-03-13 06:08:44.0002017-03-13 06:09:00.0001
    33928887517413/03/20172017-03-13 06:08:59.0002017-03-13 06:09:13.0000
    33928887517413/03/20172017-03-13 06:09:13.0002017-03-13 06:09:26.0001
    33928887517413/03/20172017-03-13 06:09:25.0002017-03-13 06:09:45.0000
    33928887517413/03/20172017-03-13 06:09:45.0002017-03-13 06:09:57.0000
    33928887517413/03/20172017-03-13 06:09:57.0002017-03-13 06:10:17.0000
    33928887517413/03/20172017-03-13 06:10:17.0002017-03-13 06:10:29.0000
    33928887517413/03/20172017-03-13 06:10:29.0002017-03-13 06:10:45.0001
    33928887517413/03/20172017-03-13 06:10:44.0002017-03-13 06:10:58.0001
    33928887517413/03/20172017-03-13 06:10:57.0002017-03-13 06:11:09.0000
    33928887517413/03/20172017-03-13 06:11:09.0002017-03-13 06:11:27.0000
    33928887517413/03/20172017-03-13 06:11:27.0002017-03-13 06:11:38.0001
    33928887517413/03/20172017-03-13 06:11:37.0002017-03-13 06:11:41.0000

    This is my Query:

    SELECT
    aseld6.ASSIGNMENT
    , ASSOC_ID
    , DATE_PICKING_START
    --, TIME_PICKING_START
    --, DATE_PICKING_COM
    --, TIME_PICKING_COM
    ,CONVERT(DATETIME, DATE_PICKING_START, 103) + CONVERT(DATETIME, TIME_PICKING_START, 108) as PickingStart
    ,CONVERT(DATETIME, DATE_PICKING_COM, 103) + CONVERT(DATETIME, TIME_PICKING_COM, 108) as PickingEnd
    --,DATEDIFF(SECOND, CONVERT(DATETIME, DATE_PICKING_START, 103) + CONVERT(DATETIME, TIME_PICKING_START, 108), CONVERT(DATETIME, DATE_PICKING_COM, 103) + CONVERT(DATETIME, TIME_PICKING_COM, 108))
    FROM
    dbo.aseld6
    where DATE_PICKING_START = '13/03/2017' and ASSIGNMENT in('33928887', '33928858', '33928890')
    order by ASSOC_ID, TIME_PICKING_START

    Does anyone have an idea on how to fix this problem please?
    I hope I've explained my problem sufficient.
    Kind Regards,
    Monique

  • March 22, 2017 at 8:06 am

    #1934298

    Have a look at LAG (Transact-SQL) and LEAD (Transact-SQL). Tone of these will be what you want (depending on where you want the data).

    Your field will then look something like this:
    DATEDIFF(SECOND, PickingStart, LAG(PickingEnd) OVER (PARTITION BY aseld6.ASSIGNMENT ORDER BY ASSOC_ID, TIME_PICKING_START)) AS PickingTimeDiff

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • March 22, 2017 at 8:15 am

    #1934300

    Hi Thom,

    Thank you for that quick reply, your suggestion works but the result is on the wrong line.
    The result from the first calculation is written to the second record instead of the first record.

    ASSIGNMENT    ASSOC_ID    DATE_PICKING_START    PickingStart    PickingEnd    PickingTimeDiff
    33928858    4265    13/03/2017    2017-03-13 05:58:53.000    2017-03-13 06:02:41.000    NULL
    33928858    4265    13/03/2017    2017-03-13 06:02:40.000    2017-03-13 06:02:54.000    1
    33928858    4265    13/03/2017    2017-03-13 06:02:52.000    2017-03-13 06:03:09.000    2

    Can this also be fixed?
    Kind regards,
    Monique

  • March 22, 2017 at 8:32 am

    #1934310

    monique.van.kolen - Wednesday, March 22, 2017 8:15 AM

    Hi Thom,

    Thank you for that quick reply, your suggestion works but the result is on the wrong line.
    The result from the first calculation is written to the second record instead of the first record.

    ASSIGNMENT    ASSOC_ID    DATE_PICKING_START    PickingStart    PickingEnd    PickingTimeDiff
    33928858    4265    13/03/2017    2017-03-13 05:58:53.000    2017-03-13 06:02:41.000    NULL
    33928858    4265    13/03/2017    2017-03-13 06:02:40.000    2017-03-13 06:02:54.000    1
    33928858    4265    13/03/2017    2017-03-13 06:02:52.000    2017-03-13 06:03:09.000    2

    Can this also be fixed?
    Kind regards,
    Monique

    Use LEAD, not LAG. They effectively do the same thing, but one looks forward in the dataset, the other backwards.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • March 22, 2017 at 8:52 am

    #1934315

    Thom A - Wednesday, March 22, 2017 8:32 AM

    monique.van.kolen - Wednesday, March 22, 2017 8:15 AM

    Hi Thom,

    Thank you for that quick reply, your suggestion works but the result is on the wrong line.
    The result from the first calculation is written to the second record instead of the first record.

    ASSIGNMENT    ASSOC_ID    DATE_PICKING_START    PickingStart    PickingEnd    PickingTimeDiff
    33928858    4265    13/03/2017    2017-03-13 05:58:53.000    2017-03-13 06:02:41.000    NULL
    33928858    4265    13/03/2017    2017-03-13 06:02:40.000    2017-03-13 06:02:54.000    1
    33928858    4265    13/03/2017    2017-03-13 06:02:52.000    2017-03-13 06:03:09.000    2

    Can this also be fixed?
    Kind regards,
    Monique

    Use LEAD, not LAG. They effectively do the same thing, but one looks forward in the dataset, the other backwards.

    Hi Thom,

    Sorry but Lead gives me completly different results that aren't correct:
    ASSIGNMENT    ASSOC_ID    DATE_PICKING_START    PickingStart    PickingEnd    PickingTimeDiff
    33928858    4265    13/03/2017    2017-03-13 05:58:53.000    2017-03-13 06:02:41.000    241
    33928858    4265    13/03/2017    2017-03-13 06:02:40.000    2017-03-13 06:02:54.000    29
    33928858    4265    13/03/2017    2017-03-13 06:02:52.000    2017-03-13 06:03:09.000    28
    33928858    4265    13/03/2017    2017-03-13 06:03:09.000    2017-03-13 06:03:20.000    20
    33928858    4265    13/03/2017    2017-03-13 06:03:18.000    2017-03-13 06:03:29.000    23
    33928858    4265    13/03/2017    2017-03-13 06:03:29.000    2017-03-13 06:03:41.000    24
    33928858    4265    13/03/2017    2017-03-13 06:03:41.000    2017-03-13 06:03:53.000    32
    33928858    4265    13/03/2017    2017-03-13 06:03:52.000    2017-03-13 06:04:13.000    44
    33928858    4265    13/03/2017    2017-03-13 06:04:13.000    2017-03-13 06:04:36.000    25
    33928858    4265    13/03/2017    2017-03-13 06:04:34.000    2017-03-13 06:04:38.000    NULL
    33928890    4265    13/03/2017    2017-03-13 06:05:29.000    2017-03-13 06:09:42.000    268
    33928890    4265    13/03/2017    2017-03-13 06:09:41.000    2017-03-13 06:09:57.000    26
    33928890    4265    13/03/2017    2017-03-13 06:09:57.000    2017-03-13 06:10:07.000    53
    33928890    4265    13/03/2017    2017-03-13 06:10:07.000    2017-03-13 06:10:50.000    71
    33928890    4265    13/03/2017    2017-03-13 06:10:49.000    2017-03-13 06:11:18.000    32
    33928890    4265    13/03/2017    2017-03-13 06:11:17.000    2017-03-13 06:11:21.000    NULL
    33928887    5174    13/03/2017    2017-03-13 06:04:07.000    2017-03-13 06:08:45.000    293
    33928887    5174    13/03/2017    2017-03-13 06:08:44.000    2017-03-13 06:09:00.000    29
    33928887    5174    13/03/2017    2017-03-13 06:08:59.000    2017-03-13 06:09:13.000    27
    33928887    5174    13/03/2017    2017-03-13 06:09:13.000    2017-03-13 06:09:26.000    32
    33928887    5174    13/03/2017    2017-03-13 06:09:25.000    2017-03-13 06:09:45.000    32
    33928887    5174    13/03/2017    2017-03-13 06:09:45.000    2017-03-13 06:09:57.000    32
    33928887    5174    13/03/2017    2017-03-13 06:09:57.000    2017-03-13 06:10:17.000    32
    33928887    5174    13/03/2017    2017-03-13 06:10:17.000    2017-03-13 06:10:29.000    28
    33928887    5174    13/03/2017    2017-03-13 06:10:29.000    2017-03-13 06:10:45.000    29
    33928887    5174    13/03/2017    2017-03-13 06:10:44.000    2017-03-13 06:10:58.000    25
    33928887    5174    13/03/2017    2017-03-13 06:10:57.000    2017-03-13 06:11:09.000    30
    33928887    5174    13/03/2017    2017-03-13 06:11:09.000    2017-03-13 06:11:27.000    29
    33928887    5174    13/03/2017    2017-03-13 06:11:27.000    2017-03-13 06:11:38.000    14
    33928887    5174    13/03/2017    2017-03-13 06:11:37.000    2017-03-13 06:11:41.000    NULL

    Monique

  • March 22, 2017 at 9:22 am

    #1934318

    you havent posted the code you are using....only the results .

    maybe this will help you


    CREATE TABLE #yourtable(
     ASSIGNMENT   INT
    ,PickingStart   DATETIME
    ,PickingEnd   DATETIME
    );
    INSERT INTO #yourtable(ASSIGNMENT,PickingStart,PickingEnd) VALUES
    (33928858,'2017-03-13 05:58:53','2017-03-13 06:02:41'),(33928858,'2017-03-13 06:02:40','2017-03-13 06:02:54')
    ,(33928858,'2017-03-13 06:02:52','2017-03-13 06:03:09'),(33928858,'2017-03-13 06:03:09','2017-03-13 06:03:20')
    ,(33928858,'2017-03-13 06:03:18','2017-03-13 06:03:29'),(33928858,'2017-03-13 06:03:29','2017-03-13 06:03:41')
    ,(33928858,'2017-03-13 06:03:41','2017-03-13 06:03:53'),(33928858,'2017-03-13 06:03:52','2017-03-13 06:04:13')
    ,(33928858,'2017-03-13 06:04:13','2017-03-13 06:04:36'),(33928858,'2017-03-13 06:04:34','2017-03-13 06:04:38')
    ,(33928890,'2017-03-13 06:05:29','2017-03-13 06:09:42'),(33928890,'2017-03-13 06:09:41','2017-03-13 06:09:57')
    ,(33928890,'2017-03-13 06:09:57','2017-03-13 06:10:07'),(33928890,'2017-03-13 06:10:07','2017-03-13 06:10:50')
    ,(33928890,'2017-03-13 06:10:49','2017-03-13 06:11:18'),(33928890,'2017-03-13 06:11:17','2017-03-13 06:11:21')
    ,(33928887,'2017-03-13 06:04:07','2017-03-13 06:08:45'),(33928887,'2017-03-13 06:08:44','2017-03-13 06:09:00')
    ,(33928887,'2017-03-13 06:08:59','2017-03-13 06:09:13'),(33928887,'2017-03-13 06:09:13','2017-03-13 06:09:26')
    ,(33928887,'2017-03-13 06:09:25','2017-03-13 06:09:45'),(33928887,'2017-03-13 06:09:45','2017-03-13 06:09:57')
    ,(33928887,'2017-03-13 06:09:57','2017-03-13 06:10:17'),(33928887,'2017-03-13 06:10:17','2017-03-13 06:10:29')
    ,(33928887,'2017-03-13 06:10:29','2017-03-13 06:10:45'),(33928887,'2017-03-13 06:10:44','2017-03-13 06:10:58')
    ,(33928887,'2017-03-13 06:10:57','2017-03-13 06:11:09'),(33928887,'2017-03-13 06:11:09','2017-03-13 06:11:27')
    ,(33928887,'2017-03-13 06:11:27','2017-03-13 06:11:38'),(33928887,'2017-03-13 06:11:37','2017-03-13 06:11:41');

    SELECT ASSIGNMENT,
       PickingStart,
       PickingEnd,
       DATEDIFF(SECOND, LEAD(pickingstart, 1, NULL) OVER(PARTITION BY ASSIGNMENT ORDER BY PickingStart), Pickingend) as tdiff
    FROM #yourtable;

    DROP TABLE #yourtable

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

  • March 22, 2017 at 9:55 am

    #1934320

    J Livingston SQL - Wednesday, March 22, 2017 9:22 AM

    you havent posted the code you are using....only the results .

    maybe this will help you


    CREATE TABLE #yourtable(
     ASSIGNMENT   INT
    ,PickingStart   DATETIME
    ,PickingEnd   DATETIME
    );
    INSERT INTO #yourtable(ASSIGNMENT,PickingStart,PickingEnd) VALUES
    (33928858,'2017-03-13 05:58:53','2017-03-13 06:02:41'),(33928858,'2017-03-13 06:02:40','2017-03-13 06:02:54')
    ,(33928858,'2017-03-13 06:02:52','2017-03-13 06:03:09'),(33928858,'2017-03-13 06:03:09','2017-03-13 06:03:20')
    ,(33928858,'2017-03-13 06:03:18','2017-03-13 06:03:29'),(33928858,'2017-03-13 06:03:29','2017-03-13 06:03:41')
    ,(33928858,'2017-03-13 06:03:41','2017-03-13 06:03:53'),(33928858,'2017-03-13 06:03:52','2017-03-13 06:04:13')
    ,(33928858,'2017-03-13 06:04:13','2017-03-13 06:04:36'),(33928858,'2017-03-13 06:04:34','2017-03-13 06:04:38')
    ,(33928890,'2017-03-13 06:05:29','2017-03-13 06:09:42'),(33928890,'2017-03-13 06:09:41','2017-03-13 06:09:57')
    ,(33928890,'2017-03-13 06:09:57','2017-03-13 06:10:07'),(33928890,'2017-03-13 06:10:07','2017-03-13 06:10:50')
    ,(33928890,'2017-03-13 06:10:49','2017-03-13 06:11:18'),(33928890,'2017-03-13 06:11:17','2017-03-13 06:11:21')
    ,(33928887,'2017-03-13 06:04:07','2017-03-13 06:08:45'),(33928887,'2017-03-13 06:08:44','2017-03-13 06:09:00')
    ,(33928887,'2017-03-13 06:08:59','2017-03-13 06:09:13'),(33928887,'2017-03-13 06:09:13','2017-03-13 06:09:26')
    ,(33928887,'2017-03-13 06:09:25','2017-03-13 06:09:45'),(33928887,'2017-03-13 06:09:45','2017-03-13 06:09:57')
    ,(33928887,'2017-03-13 06:09:57','2017-03-13 06:10:17'),(33928887,'2017-03-13 06:10:17','2017-03-13 06:10:29')
    ,(33928887,'2017-03-13 06:10:29','2017-03-13 06:10:45'),(33928887,'2017-03-13 06:10:44','2017-03-13 06:10:58')
    ,(33928887,'2017-03-13 06:10:57','2017-03-13 06:11:09'),(33928887,'2017-03-13 06:11:09','2017-03-13 06:11:27')
    ,(33928887,'2017-03-13 06:11:27','2017-03-13 06:11:38'),(33928887,'2017-03-13 06:11:37','2017-03-13 06:11:41');

    SELECT ASSIGNMENT,
       PickingStart,
       PickingEnd,
       DATEDIFF(SECOND, LEAD(pickingstart, 1, NULL) OVER(PARTITION BY ASSIGNMENT ORDER BY PickingStart), Pickingend) as tdiff
    FROM #yourtable;

    DROP TABLE #yourtable

    When I run #yourtable the LEAD function works as you said.

    This is my Query with the wrong results:


    SELECT
    aseld6.ASSIGNMENT
    , ASSOC_ID
    , DATE_PICKING_START
    ,CONVERT(DATETIME, DATE_PICKING_START, 103) + CONVERT(DATETIME, TIME_PICKING_START, 108) as PickingStart
    ,CONVERT(DATETIME, DATE_PICKING_COM, 103) + CONVERT(DATETIME, TIME_PICKING_COM, 108) as PickingEnd
    ,DATEDIFF(SECOND, CONVERT(DATETIME, DATE_PICKING_START, 103) + CONVERT(DATETIME, TIME_PICKING_START, 108),
     LEAD(CONVERT(DATETIME, DATE_PICKING_COM, 103) + CONVERT(DATETIME, TIME_PICKING_COM, 108))
     OVER (PARTITION BY aseld6.ASSIGNMENT ORDER BY ASSOC_ID, TIME_PICKING_START)) AS PickingTimeDiff
    FROM
    dbo.aseld6

  • March 22, 2017 at 10:17 am

    #1934323

    A...you have the datediff statement set up in the opposite order to my example
    B...you are ordering by TIME_PICKING_START .....maybe this should be DATE_PICKING_START, TIME_PICKING_START ?
    C...without seeing example of your base table definition and some sample data....we could be going back and forth for ages...please read this and post accordingly  
    https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

    thanks

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

Viewing 8 posts - 1 through 7 (of 7 total)

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