SQLTeam.com | Weblogs | Forums

How to get the sum of hrs. logged for today, past 1-week, this current month, & current quarter

Hello SQLTeam members,

Good day. May I asked for the help of any one regarding this matter. Here is the sample table I have with the client:

image

Since there is only one table that has a datetime field (in SystemAppUse table) and whether it is login or logout determines by UseType Id, I want to collect such information of sum of hours to arrive like this one output below (assumptions on numbers):

Thank you.

Mark

hope this helps :slight_smile:

 select 
      empid ,  datediff(hours , a.createddate , b.createddate )
    from 
         ( select createddate from table where usetype = 4 )  
               join 
        ( select createdate  from table where usetype = 1 ) a 
    on 
       a.employe id  = employee id

Hi, welcome to the forum!

It's considered polite to post directly usable data that can be used to test the code we write, rather than just a display of the data. Like this:

CREATE TABLE #Employee ( ID int NOT NULL, Name varchar(100) NOT NULL );
INSERT INTO #Employee VALUES
    (1, 'John Smith'),
    (2, 'John Doe');

CREATE TABLE #SystemAppUse ( ID int NOT NULL, UseType smallint NOT NULL, EmployeeID int NOT NULL, CreatedDate datetime NOT NULL )
INSERT INTO #SystemAppUse VALUES
    (1, 4, 1, '20210414 16:49:12'),
    (2, 1, 1, '20210414 16:48:57');

CREATE TABLE #UseType ( ID smallint NOT NULL, Description varchar(100) NOT NULL );
INSERT INTO #UseType VALUES
    (1, 'Admin-Login'),
    (2, 'Employee-Login'), 
    (3, 'Cashier-Login'), 
    (4, 'Logout');

With that out of the way, here's the actual code to calc the work hours:

SELECT SAU.EmployeeID, 
    CAST(SUM(DATEDIFF(SECOND, '20100101', SAU.CreatedDate) * 
    CASE WHEN UT.Description LIKE '%Logout%' THEN 1 ELSE -1 END) 
        / 60.0 / 60.0 AS decimal(9, 2)) AS Hours_Worked,
    CASE WHEN 
        SUM(CASE WHEN UT.Description LIKE '%Logout%' THEN 0 ELSE 1 END) =
        SUM(CASE WHEN UT.Description LIKE '%Logout%' THEN 1 ELSE 0 END) 
        THEN 'Login and Logout counts match, hours are good.' 
        ELSE 'Login and Logout counts do NOT match, hours could NOT be accurately calculated.' END AS Message
FROM #SystemAppUse SAU
INNER JOIN #UseType UT ON UT.ID = SAU.UseType
GROUP BY SAU.EmployeeID
1 Like

[harishgg1] / [ScottPletcher]:

Hello. Thank you for the SQL you've given, definitely will try both. I just wonder how to get the very first login and the last logout from createdDate (per day) in #SystemAppUse table? So getting them I could compute the work hours.

Thanks guys.

using
min for login
max for logout
= per person = or all = depends on your requirement

You don't have to match up by day. That was the point of my code. Instead, you can calc the difference between those values and a base date to get the time worked.

As a generic example, say the login time is Tues at 8:45AM. The logout time is Tues at 6:15PM. If I subtract that directly, I get 9.5 hours. But, instead, say I have a base date of Monday at midnight. Then I calc the hours from the base to the logout time and subtract the hours from the base to the login time. That is still 9.5 hours, the correct answer, but I don't have to do the complex task of matching the login and logout time to each other.

As long as the data is accurate, one login with a matching one logout, this method works.

ScottPletcher:

Hello, hope all is well. Thank you for explaining. I want to ask how to group the hours so I could have the following columns:

total today
total last 7 weeks
total month
total this quarter

Also, per day, a user can have multiple logins and logout because this counting will be use as how many "hours" the user used this web app I am creating. Though there's a challenge of calling this query when the user closes the web browser. For the meantime, this will be logged every time the user logins and clicks the logout button for logout, and/or when the user's session had expired.

Thank you.