Post reply

Sum Time in Sql

  • September 4, 2016 at 5:10 am

    #317059

    I am trying to sum up time and almost did it. Now I would like to sum up top 2 time as follows:

    SELECT TOP 2*,

    ISNULL((RIGHT('00' + CONVERT(VARCHAR(10), SUM(DATEDIFF(MINUTE, FromTime, ToTime)) / 60), 2)

    + ':' +

    RIGHT('00' + CONVERT(VARCHAR(2), SUM(DATEDIFF(Minute, FromTime, ToTime)) % 60), 2)

    + ':' +

    RIGHT('00' + CONVERT(VARCHAR(2), SUM(DATEDIFF(SECOND, FromTime, ToTime)) % 60), 2)), 0)

    AS TotalTime FROM Demo GROUP BY ID, FromTime, ToTime

    The output is as follows:

    So is there any way or still possible to add the sum of the TotalTime column at the end? I would appreciate if any idea is shared. Thanks.

  • September 4, 2016 at 6:43 am

    #1899115

    Quick thought, the time calculation is not correct, the second result sould be 02:30:21 so there is an extra minute there in the results.

    😎

    Here are three different methods

    USE TEEST;

    GO

    SET NOCOUNT ON;

    ;WITH DEMO(ID,FromTime,ToTime) AS

    (

    SELECT

    ID

    ,CONVERT(DATETIME,FromTime,120) AS FromTime

    ,CONVERT(DATETIME,ToTime,120) AS ToTime

    FROM (VALUES (1,'2016-09-03 18:14:47.000','2016-09-03 20:32:54.000')

    ,(2,'2016-09-03 12:35:45.000','2016-09-03 15:06:06.000')

    ) AS X(ID,FromTime,ToTime)

    )

    SELECT

    D.ID

    ,D.FromTime

    ,D.ToTime

    ,CONVERT(TIME(0),D.ToTime - D.FromTime,0) AS TIME_DIFF_01

    ,DATEADD(SECOND,DATEDIFF(SECOND,D.FromTime,D.ToTime),CONVERT(TIME(0),'00:00:00',0)) AS TIME_DIFF_02

    ,CONVERT(TIME(0),DATEADD(SECOND,DATEDIFF(SECOND,D.FromTime,D.ToTime),CONVERT(DATETIME,0,0)),0) AS TIME_DIFF_03

    ,CONVERT(TIME(0),CONVERT(DATETIME,SUM(CONVERT(FLOAT,D.ToTime,0) - CONVERT(FLOAT,D.FromTime,0))OVER

    (

    PARTITION BY (SELECT NULL)

    ),0),0) AS TIME_SUM_01

    ,DATEADD(SECOND,SUM(DATEDIFF(SECOND,D.FromTime,D.ToTime)) OVER

    (

    PARTITION BY (SELECT NULL)

    ),CONVERT(TIME(0),'00:00:00',0)) AS TIME_SUM_02

    ,CONVERT(TIME(0),DATEADD(SECOND,SUM(DATEDIFF(SECOND,D.FromTime,D.ToTime)) OVER

    (

    PARTITION BY (SELECT NULL)

    ),CONVERT(DATETIME,0,0)),0) AS TIME_SUM_03

    FROM DEMO D;

    Output

    ID FromTime ToTime TIME_DIFF_01 TIME_DIFF_02 TIME_DIFF_03 TIME_SUM_01 TIME_SUM_02 TIME_SUM_03

    ----------- ----------------------- ----------------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------

    1 2016-09-03 18:14:47.000 2016-09-03 20:32:54.000 02:18:07 02:18:07 02:18:07 04:48:28 04:48:28 04:48:28

    2 2016-09-03 12:35:45.000 2016-09-03 15:06:06.000 02:30:21 02:30:21 02:30:21 04:48:28 04:48:28 04:48:28

  • September 4, 2016 at 7:04 am

    #1899116

    Thanks a lot. Works perfectly 🙂 🙂

  • September 4, 2016 at 7:19 am

    #1899117

    do you have time differences of greater than 24 hours?

    eg

    ;WITH DEMO(ID,FromTime,ToTime) AS

    (

    SELECT

    ID

    ,CONVERT(DATETIME,FromTime,120) AS FromTime

    ,CONVERT(DATETIME,ToTime,120) AS ToTime

    FROM (VALUES (1,'2016-09-03 23:59:59.000','2016-09-04 00:00:01.000')

    ,(2,'2016-09-01 12:35:45.000','2016-09-04 15:06:06.000')

    ,(3,'2016-09-01 12:35:45.000','2016-09-02 12:35:44.000')

    ,(4,'2016-09-01 12:35:45.000','2016-09-02 12:35:45.000')

    ,(5,'2016-09-01 12:35:45.000','2016-09-02 12:35:46.000')

    ) AS X(ID,FromTime,ToTime)

    )

    SELECT

    D.ID

    ,D.FromTime

    ,D.ToTime

    ,CONVERT(TIME(0),D.ToTime - D.FromTime,0) AS TIME_DIFF_01

    ,DATEADD(SECOND,DATEDIFF(SECOND,D.FromTime,D.ToTime),CONVERT(TIME(0),'00:00:00',0)) AS TIME_DIFF_02

    ,CONVERT(TIME(0),DATEADD(SECOND,DATEDIFF(SECOND,D.FromTime,D.ToTime),CONVERT(DATETIME,0,0)),0) AS TIME_DIFF_03

    ,CONVERT(TIME(0),CONVERT(DATETIME,SUM(CONVERT(FLOAT,D.ToTime,0) - CONVERT(FLOAT,D.FromTime,0))OVER

    (

    PARTITION BY (SELECT NULL)

    ),0),0) AS TIME_SUM_01

    ,DATEADD(SECOND,SUM(DATEDIFF(SECOND,D.FromTime,D.ToTime)) OVER

    (

    PARTITION BY (SELECT NULL)

    ),CONVERT(TIME(0),'00:00:00',0)) AS TIME_SUM_02

    ,CONVERT(TIME(0),DATEADD(SECOND,SUM(DATEDIFF(SECOND,D.FromTime,D.ToTime)) OVER

    (

    PARTITION BY (SELECT NULL)

    ),CONVERT(DATETIME,0,0)),0) AS TIME_SUM_03

    FROM DEMO D;

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

  • September 4, 2016 at 7:41 am

    #1899118

    J Livingston SQL (9/4/2016)

    do you have time differences of greater than 24 hours?

    Good point:pinch:, obviously the TIME data type isn't fit for purpose if either a single interval or the sum of intervals exceeds 24 hours.

    😎

  • September 4, 2016 at 8:23 am

    #1899119

    This code will return the interval in the format of DD:HH:MM:SS

    😎

    USE TEEST;

    GO

    SET NOCOUNT ON;

    ;WITH DEMO(ID,FromTime,ToTime) AS

    (

    SELECT

    ID

    ,CONVERT(DATETIME,FromTime,120) AS FromTime

    ,CONVERT(DATETIME,ToTime,120) AS ToTime

    FROM (VALUES (1,'2016-09-03 23:59:59.000','2016-09-04 00:00:01.000')

    ,(2,'2016-09-01 12:35:45.000','2016-09-04 15:06:06.000')

    ,(3,'2016-09-01 12:35:45.000','2016-09-02 12:35:44.000')

    ,(4,'2016-09-01 12:35:45.000','2016-09-02 12:35:45.000')

    ,(5,'2016-09-01 12:35:45.000','2016-09-02 12:35:46.000')

    ) AS X(ID,FromTime,ToTime)

    )

    ,BASE_DATA AS

    (

    SELECT

    D.ID

    ,D.FromTime

    ,D.ToTime

    ,DATEDIFF(SECOND,D.FromTime,D.ToTime) AS TIME_DIFF_SECOND

    ,SUM(DATEDIFF(SECOND,D.FromTime,D.ToTime)) OVER

    (

    PARTITION BY (SELECT NULL)

    ) AS TIME_DIFF_SUM

    FROM DEMO D

    )

    SELECT

    BD.ID

    ,BD.FromTime

    ,BD.ToTime

    ,CONVERT(VARCHAR(12),DATEDIFF(DAY,0,DATEADD(SECOND,BD.TIME_DIFF_SECOND,0)),0) + CHAR(58)

    + CONVERT(CHAR(8),CONVERT(TIME(0),DATEADD(SECOND,BD.TIME_DIFF_SECOND,0),0),120) AS DDHHMMSS

    ,CONVERT(VARCHAR(12),DATEDIFF(DAY,0,DATEADD(SECOND,BD.TIME_DIFF_SUM,0)),0) + CHAR(58)

    + CONVERT(CHAR(8),CONVERT(TIME(0),DATEADD(SECOND,BD.TIME_DIFF_SUM,0),0),120) AS SUM_DDHHMMSS

    FROM BASE_DATA BD

    ;

    Output

    ID FromTime ToTime DDHHMMSS SUM_DDHHMMSS

    ----------- ----------------------- ----------------------- --------------------- ---------------------

    1 2016-09-03 23:59:59.000 2016-09-04 00:00:01.000 0:00:00:02 6:02:30:23

    2 2016-09-01 12:35:45.000 2016-09-04 15:06:06.000 3:02:30:21 6:02:30:23

    3 2016-09-01 12:35:45.000 2016-09-02 12:35:44.000 0:23:59:59 6:02:30:23

    4 2016-09-01 12:35:45.000 2016-09-02 12:35:45.000 1:00:00:00 6:02:30:23

    5 2016-09-01 12:35:45.000 2016-09-02 12:35:46.000 1:00:00:01 6:02:30:23

  • September 4, 2016 at 9:15 am

    #1899123

    Thanks all for the answers. Just wondering why the below query didn't work using TOP 2 directly:

    SELECT TOP 2

    k.ID,

    k.FromTime,

    k.ToTime,

    DATEADD(SECOND,DATEDIFF(SECOND, k.FromTime, k.ToTime),CONVERT(TIME(0),'00:00:00',0)) AS TIME_DIFF,

    DATEADD(SECOND,SUM(DATEDIFF(SECOND, k.FromTime, k.ToTime)) OVER

    (

    PARTITION BY (SELECT NULL)

    ),CONVERT(TIME(0),'00:00:00',0)) AS TIME_SUM

    FROM Demo k;

    But in the below case, it almost creates a new table say 'Hello' to select the TOP 2 data or records:

    ;WITH Hello AS

    (

    SELECT TOP 1

    ID, FromTime, ToTime

    FROM Demo

    )

    SELECT TOP 2

    k.ID,

    k.FromTime,

    k.ToTime,

    DATEADD(SECOND,DATEDIFF(SECOND, k.FromTime, k.ToTime),CONVERT(TIME(0),'00:00:00',0)) AS TIME_DIFF,

    DATEADD(SECOND,SUM(DATEDIFF(SECOND, k.FromTime, k.ToTime)) OVER

    (

    PARTITION BY (SELECT NULL)

    ),CONVERT(TIME(0),'00:00:00',0)) AS TIME_SUM

    FROM Hello k;

    Is there any reason behind it? Just want to know. Thanks.

  • September 4, 2016 at 9:29 am

    #1899124

    Simply add the grouping as the first CTE

    😎

    USE TEEST;

    GO

    SET NOCOUNT ON;

    ;WITH DEMO(ID,FromTime,ToTime) AS

    (

    SELECT

    ID

    ,CONVERT(DATETIME,FromTime,120) AS FromTime

    ,CONVERT(DATETIME,ToTime,120) AS ToTime

    FROM (VALUES (1,'2016-09-03 23:59:59.000','2016-09-04 00:00:01.000')

    ,(2,'2016-09-01 12:35:45.000','2016-09-04 15:06:06.000')

    ,(3,'2016-09-01 12:35:45.000','2016-09-02 12:35:44.000')

    ,(4,'2016-09-01 12:35:45.000','2016-09-02 12:35:45.000')

    ,(5,'2016-09-01 12:35:45.000','2016-09-02 12:35:46.000')

    ) AS X(ID,FromTime,ToTime)

    )

    ,FIRST_GROUP AS

    (

    SELECT

    TOP(2)

    DD.ID

    ,DD.FromTime

    ,DD.ToTime

    FROM DEMO DD

    ORDER BY DATEDIFF(SECOND,DD.FromTime,DD.ToTime) DESC

    )

    ,BASE_DATA AS

    (

    SELECT

    FG.ID

    ,FG.FromTime

    ,FG.ToTime

    ,DATEDIFF(SECOND,FG.FromTime,FG.ToTime) AS TIME_DIFF_SECOND

    ,SUM(DATEDIFF(SECOND,FG.FromTime,FG.ToTime)) OVER

    (

    PARTITION BY (SELECT NULL)

    ) AS TIME_DIFF_SUM

    FROM FIRST_GROUP FG

    )

    SELECT

    BD.ID

    ,BD.FromTime

    ,BD.ToTime

    ,CONVERT(VARCHAR(12),DATEDIFF(DAY,0,DATEADD(SECOND,BD.TIME_DIFF_SECOND,0)),0) + CHAR(58)

    + CONVERT(CHAR(8),CONVERT(TIME(0),DATEADD(SECOND,BD.TIME_DIFF_SECOND,0),0),120) AS DDHHMMSS

    ,CONVERT(VARCHAR(12),DATEDIFF(DAY,0,DATEADD(SECOND,BD.TIME_DIFF_SUM,0)),0) + CHAR(58)

    + CONVERT(CHAR(8),CONVERT(TIME(0),DATEADD(SECOND,BD.TIME_DIFF_SUM,0),0),120) AS SUM_DDHHMMSS

    FROM BASE_DATA BD

    ;

    Output

    ID FromTime ToTime DDHHMMSS SUM_DDHHMMSS

    ----------- ----------------------- ----------------------- --------------------- ---------------------

    2 2016-09-01 12:35:45.000 2016-09-04 15:06:06.000 3:02:30:21 4:02:30:22

    5 2016-09-01 12:35:45.000 2016-09-02 12:35:46.000 1:00:00:01 4:02:30:22

  • September 4, 2016 at 1:51 pm

    #1899139

    Eirikur Eiriksson (9/4/2016)

    This code will return the interval in the format of DD:HH:MM:SS

    😎

    USE TEEST;

    GO

    SET NOCOUNT ON;

    ;WITH DEMO(ID,FromTime,ToTime) AS

    (

    SELECT

    ID

    ,CONVERT(DATETIME,FromTime,120) AS FromTime

    ,CONVERT(DATETIME,ToTime,120) AS ToTime

    FROM (VALUES (1,'2016-09-03 23:59:59.000','2016-09-04 00:00:01.000')

    ,(2,'2016-09-01 12:35:45.000','2016-09-04 15:06:06.000')

    ,(3,'2016-09-01 12:35:45.000','2016-09-02 12:35:44.000')

    ,(4,'2016-09-01 12:35:45.000','2016-09-02 12:35:45.000')

    ,(5,'2016-09-01 12:35:45.000','2016-09-02 12:35:46.000')

    ) AS X(ID,FromTime,ToTime)

    )

    ,BASE_DATA AS

    (

    SELECT

    D.ID

    ,D.FromTime

    ,D.ToTime

    ,DATEDIFF(SECOND,D.FromTime,D.ToTime) AS TIME_DIFF_SECOND

    ,SUM(DATEDIFF(SECOND,D.FromTime,D.ToTime)) OVER

    (

    PARTITION BY (SELECT NULL)

    ) AS TIME_DIFF_SUM

    FROM DEMO D

    )

    SELECT

    BD.ID

    ,BD.FromTime

    ,BD.ToTime

    ,CONVERT(VARCHAR(12),DATEDIFF(DAY,0,DATEADD(SECOND,BD.TIME_DIFF_SECOND,0)),0) + CHAR(58)

    + CONVERT(CHAR(8),CONVERT(TIME(0),DATEADD(SECOND,BD.TIME_DIFF_SECOND,0),0),120) AS DDHHMMSS

    ,CONVERT(VARCHAR(12),DATEDIFF(DAY,0,DATEADD(SECOND,BD.TIME_DIFF_SUM,0)),0) + CHAR(58)

    + CONVERT(CHAR(8),CONVERT(TIME(0),DATEADD(SECOND,BD.TIME_DIFF_SUM,0),0),120) AS SUM_DDHHMMSS

    FROM BASE_DATA BD

    ;

    Output

    ID FromTime ToTime DDHHMMSS SUM_DDHHMMSS

    ----------- ----------------------- ----------------------- --------------------- ---------------------

    1 2016-09-03 23:59:59.000 2016-09-04 00:00:01.000 0:00:00:02 6:02:30:23

    2 2016-09-01 12:35:45.000 2016-09-04 15:06:06.000 3:02:30:21 6:02:30:23

    3 2016-09-01 12:35:45.000 2016-09-02 12:35:44.000 0:23:59:59 6:02:30:23

    4 2016-09-01 12:35:45.000 2016-09-02 12:35:45.000 1:00:00:00 6:02:30:23

    5 2016-09-01 12:35:45.000 2016-09-02 12:35:46.000 1:00:00:01 6:02:30:23

    thanks Eirikur .. seems sound.. a keeper for me.

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

  • September 4, 2016 at 9:54 pm

    #1899151

    There is a much simpler way to find time difference between 2 datetime values:

    TimeDifference = TimeTo - TimeFrom

    THis will allow to count milliseconds too:

    ;WITH DEMO(ID,FromTime,ToTime) AS

    (

    SELECT

    ID

    ,CONVERT(DATETIME,FromTime,120) AS FromTime

    ,CONVERT(DATETIME,ToTime,120) AS ToTime

    FROM (VALUES (1,'2016-09-03 23:59:59.000','2016-09-04 00:00:01.100')

    ,(2,'2016-09-01 12:35:45.000','2016-09-04 15:06:06.020')

    ,(3,'2016-09-01 12:35:45.000','2016-09-02 12:35:44.003')

    ,(4,'2016-09-01 12:35:45.000','2016-09-02 12:35:45.400')

    ,(5,'2016-09-01 12:35:45.000','2016-09-02 12:35:46.050')

    ) AS X(ID,FromTime,ToTime)

    )

    SELECT

    DD.ID ,DD.FromTime ,DD.ToTime,

    Duration = DD.ToTime - DD.FromTime ,

    TotalDuration = CONVERT(DATETIME, TotalDuration) ,

    SUM_DDHHMMSS = CONVERT(VARCHAR(10), FLOOR(td.TotalDuration)) + ':'

    + CONVERT(VARCHAR(20), CONVERT(DATETIME, td.TotalDuration), 114)

    FROM DEMO DD

    CROSS JOIN (

    SELECT SUM( CONVERT(FLOAT, ToTime - FromTime)) TotalDuration

    FROM DEMO

    ) td

    ORDER BY (ToTime - FromTime) DESC

    _____________
    Code for TallyGenerator

Viewing 10 posts - 1 through 10 (of 10 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy