Post reply

difference between early start date and recent end

  • September 19, 2017 at 8:58 am

    #325436

    can someone pls help with the code to get the difference in seconds between the recent end date and earliest start date for each Rid value. data looks like below.Thanks

    ID  Rid         Start                                      End
    1    1      2016-05-10 10:48:55.027     2016-05-10 10:48:55.900
    2    1     2016-05-10 10:48:55.060      2016-05-10 10:48:54.430
    3    1      2016-05-10 10:48:55.213      NULL
    4    1      2016-05-10 10:48:56.040      2016-05-10 10:48:57.047
    5    2      2016-05-10 09:29:55.077     2016-05-10 09:30:23.977
    6    2     2016-05-10 09:29:55.157     2016-05-10 09:30:25.340
    7    2     2016-05-10 09:29:55.203     2016-05-10 09:30:26.390
    8    2     2016-05-10 09:29:55.267      2016-05-10 09:30:26.593

    Output-
    Rid    Seconds
    1         2
    2        31

  • September 19, 2017 at 9:03 am

    #1959954

    That's easy using MIN, MAX and DATEDIFF. What have you tried?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 19, 2017 at 9:04 am

    #1959956

    WITH StartandEnd AS (
        SELECT
             Rid
        ,    MIN(Start) AS Early
        ,    MAX(End) AS Late
        FROM MyTable
        GROUP BY Rid
        )
    SELECT
         Rid
    ,    DATEDIFF(second,Early,Late) AS SecDiff
    FROM StartandEnd;

    John

  • September 19, 2017 at 9:19 am

    #1959964

    John Mitchell-245523 - Tuesday, September 19, 2017 9:04 AM

    WITH StartandEnd AS (
        SELECT
             Rid
        ,    MIN(Start) AS Early
        ,    MAX(End) AS Late
        FROM MyTable
        GROUP BY Rid
        )
    SELECT
         Rid
    ,    DATEDIFF(second,Early,Late) AS SecDiff
    FROM StartandEnd;

    John

    how do i add year and qtr to it from the early date column?

    Output-
    Rid Seconds  Year  Qtr  
    1 2       2016      2
    2 31     2016      2

  • September 19, 2017 at 9:24 am

    #1959969

    Like Luis said, what have you tried?  I'm not here to do your job for you.  Hint: use the DATEPART function.

    John

  • September 19, 2017 at 9:26 am

    #1959970

    Papil - Tuesday, September 19, 2017 9:19 AM

    John Mitchell-245523 - Tuesday, September 19, 2017 9:04 AM

    WITH StartandEnd AS (
        SELECT
             Rid
        ,    MIN(Start) AS Early
        ,    MAX(End) AS Late
        FROM MyTable
        GROUP BY Rid
        )
    SELECT
         Rid
    ,    DATEDIFF(second,Early,Late) AS SecDiff
    FROM StartandEnd;

    John

    how do i add year and qtr to it from the early date column?

    Output-
    Rid Seconds  Year  Qtr  
    1 2       2016      2
    2 31     2016      2

    Look up datepart.

  • September 19, 2017 at 9:32 am

    #1959975

    Lynn Pettis - Tuesday, September 19, 2017 9:26 AM

    Papil - Tuesday, September 19, 2017 9:19 AM

    John Mitchell-245523 - Tuesday, September 19, 2017 9:04 AM

    WITH StartandEnd AS (
        SELECT
             Rid
        ,    MIN(Start) AS Early
        ,    MAX(End) AS Late
        FROM MyTable
        GROUP BY Rid
        )
    SELECT
         Rid
    ,    DATEDIFF(second,Early,Late) AS SecDiff
    FROM StartandEnd;

    John

    how do i add year and qtr to it from the early date column?

    Output-
    Rid Seconds  Year  Qtr  
    1 2       2016      2
    2 31     2016      2

    Look up datepart.

    Thanks got it to work.

  • September 19, 2017 at 9:54 am

    #1959982

    Papil - Tuesday, September 19, 2017 9:32 AM

    Thanks got it to work.

    It's considered good practice and polite to share your solution on a public forum. This would allow you to get suggestions for possible improvements and it would help others that face the same problem.
    That said, please share your solution.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 19, 2017 at 11:40 am

    #1960010

    Luis Cazares - Tuesday, September 19, 2017 9:54 AM

    Papil - Tuesday, September 19, 2017 9:32 AM

    Thanks got it to work.

    It's considered good practice and polite to share your solution on a public forum. This would allow you to get suggestions for possible improvements and it would help others that face the same problem.
    That said, please share your solution.

    here you go

    select top 1000
    DATEPART(YEAR,start) [Year],DATEPART(QUARTER,start) [Quarter],
    Rid ,datediff(second,min(start),max(end)) AS duration from Table#
    group by DATEPART(YEAR,start) ,DATEPART(QUARTER,start) , Rid
    order by DATEPART(YEAR,start) desc,DATEPART(QUARTER,start) ASC

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

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