Sum of Hours, Minutes and Seconds across same Id

I have a table which only which stores the login and logout date against a user along with their time and their datatype is datetime.

The dates are stored in the format 2021-12-17 14:05:25.597.

I am able to get the total time duration between the login and logout date but I need to sum all the rows across a particular user against a date.

Here is some sample data:

 Sno      USERID  Name    LoginDateAndTime       LogOutDateAndTime     TimeDuration   Total
 -----------------------------------------------------------------------------------
   1       222    abc     17 Dec 2021 1:40PM     17 Dec 2021 1:40PM     0:0:9         
   2       222    abc     17 Dec 2021 2:05PM     17 Dec 2021 3:07PM     1:2:4
   3       444    xyz     18 Dec 2021 3:37PM     18 Dec 2021 3:37PM     0:0:6
   4       444    xyz     16 Dec 2021 1:51PM     16 Dec 2021 2:22PM     0:30:24
   5       444    xyz     16 Dec 2021 10:17PM    16 Dec 2021 10:18PM    0:1:20

Here is my query:

SELECT
    u.userid, u.Name,
    CONVERT(varchar, la.loginDate, 106)   ' '   LTRIM(RIGHT(CONVERT(VARCHAR(20), la.loginDate, 100), 7)) AS LoginDateAndTime,
    CONVERT(varchar, la.logoutDate,106)   ' '   LTRIM(RIGHT(CONVERT(VARCHAR(20), la.logoutDate, 100), 7)) AS LogOutDateAndTime,
    CONVERT(varchar(5), DATEDIFF(s, loginDate, logoutDate) / 3600)   ':'   
       CONVERT(varchar(5), DATEDIFF(s, loginDate, logoutDate) % 3600 / 60)   ':'   
       CONVERT(varchar(5), (DATEDIFF(s, loginDate, logoutDate) % 60)) AS 'TimeDuration',
FROM
    login la
LEFT JOIN
    users u ON u.userid = la.id

Now I want to show a new column that sums up the hours and minutes across same Ids and same date.

I did multiple searches but I am still confused.

For example:

Against Id = 222 there are two entries with same date so the total would be 0:0:9 plus 1:2:4 which is 1:2:13
Against Id = 444 there are three entries with two different dates so against Sno 3 The total would be same that is 0:0:6 and against Sno 4 and 5 these two would add i.e 0:30:24 and 0:1:20 .

How can I achieve this?Anyone can help me with this? Its quite complicated

CodePudding user response：

based on your fiddles, i also created one, but currently im unable to comment, so posted it as an answer:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3980a6b2eec812d82fe9ed43339981c2

--with-clause begins
--pre as (...) is the first query within the with-clause
with pre as (
    select
        USERID,
        Name,
        CAST(loginDate AS date) AS LoginData,
        convert(varchar,loginDate,106)   ' '   LTRIM(RIGHT(CONVERT(VARCHAR(20),loginDate, 100), 7)) as LoginDateAndTime,
        convert(varchar,logoutDate,106)   ' '   LTRIM(RIGHT(CONVERT(VARCHAR(20),logoutDate, 100), 7)) as LogOutDateAndTime,
        convert(varchar(5),DateDiff(s, loginDate, logoutDate)/3600) ':' convert(varchar(5),DateDiff(s, loginDate, logoutDate)600/60) ':' convert(varchar(5),(DateDiff(s, loginDate, logoutDate)`)) as 'Time Duration Of Login [hh:mm:ss]'
    from
        login
),
--total as (...) is the second query within the with-clause
total as (
    SELECT
        userid,
        Name,
        CAST(loginDate AS date) AS LoginData,
        CONVERT(varchar(5), SUM(DATEDIFF(s, loginDate, logoutDate)) / 3600)   ':'   
        CONVERT(varchar(5), SUM(DATEDIFF(s, loginDate, logoutDate)) % 3600 / 60)   ':'   
        CONVERT(varchar(5), SUM(DATEDIFF(s, loginDate, logoutDate)) % 60) AS TotalDuration
    FROM
        login
    GROUP BY
        userid,
        Name,
        CAST(loginDate AS date)
)
--with-clause ends
select
    pre.*,
    total.totalduration
    from
        pre
    join
        total 
    on
        pre.userid = total.userid
        and total.LoginData = pre.loginData

please refer to https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15 for detailed explanation

in short words, with-clauses are used to organize and simplyfy complex sql-statements, for example: to get rid of sub-queries with select-statements; so with-clauses 'make sql-statements easier to read'

therefore the with-clause provides one ore more temporary named resultset(s), also known as common table expression (CTE), that are defined within the execution scope of a single select, insert, update, delete or merge statement

in my posted statement, i combined your query (pre) and @Charlieface s query (total)

@Charlieface s query (total) sums up the loginduration for each userid, name and logindate.

if i got you right, you wanted to achieve a query, that returns all rows from login-table expanded by the total login-duration per date and per user (id and name)

thats what the statement above does.

if i got you wrong and you want to achieve a query that return all rows from login-table expanded by the total login-duration overall dates per user (id and name), i customized the statement as follows, my customization - in relation to the statement above - are the commented lines within the statement:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d2240f4ce7364ceccbcc8eec88dfada6

with pre as (
    select
        USERID,
        Name,
        --CAST(loginDate AS date) AS LoginData,
        convert(varchar,loginDate,106)   ' '   LTRIM(RIGHT(CONVERT(VARCHAR(20),loginDate, 100), 7)) as LoginDateAndTime,
        convert(varchar,logoutDate,106)   ' '   LTRIM(RIGHT(CONVERT(VARCHAR(20),logoutDate, 100), 7)) as LogOutDateAndTime,
        convert(varchar(5),DateDiff(s, loginDate, logoutDate)/3600) ':' convert(varchar(5),DateDiff(s, loginDate, logoutDate)600/60) ':' convert(varchar(5),(DateDiff(s, loginDate, logoutDate)`)) as 'Time Duration Of Login [hh:mm:ss]'
    from
        login
),
total as (
    SELECT
        userid,
        Name,
        --CAST(loginDate AS date) AS LoginData,
        CONVERT(varchar(5), SUM(DATEDIFF(s, loginDate, logoutDate)) / 3600)   ':'   
        CONVERT(varchar(5), SUM(DATEDIFF(s, loginDate, logoutDate)) % 3600 / 60)   ':'   
        CONVERT(varchar(5), SUM(DATEDIFF(s, loginDate, logoutDate)) % 60) AS TotalDuration
    FROM
        login
    GROUP BY
        userid,
        Name
        --,
        --CAST(loginDate AS date)
)
select
    pre.*,
    total.totalduration
    from
        pre
    join
        total 
    on
        pre.userid = total.userid
        --and total.LoginData = pre.loginData

CodePudding user response：

Looks like you just need to group by user ID and date.

It's unclear why you have a LEFT JOIN here, why isn't it an INNER

SELECT
    times.userid,
    times.Name,
    times.LoginDate,
    CONVERT(varchar(5), TotalDurationS / 3600)   ':'   
       CONVERT(varchar(5), TotalDurationS % 3600 / 60)   ':'   
       CONVERT(varchar(5), TotalDurationS % 60) AS TotalDuration
FROM
(
    SELECT
      u.userid,
      u.Name,
      CAST(la.loginDate AS date) AS LoginDate,
      SUM(DATEDIFF(s, loginDate, logoutDate)) AS TotalDurationS
    FROM login la
    JOIN users u ON u.userid = la.id
    GROUP BY
      u.userid,
      u.Name,
      CAST(la.loginDate AS date)
) times;

We begin by grouping by userid and the date (which we get by casting as date)
Then we get the SUM of the DATEDIFF calculation
We then format it as a time