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

  • Dear SQLServerCentral members,

    Hello, and good day.  Hope all is well.

    May I asked for the help of any one regarding this matter.  Here is the sample table I have with the client (Please see the attached file).

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

    Name                     Today       Past 1 week        This Month            This Quarter

    John Smith             10                 20                          30                             50

     

    Thank you for any help.

    Mark

    • This topic was modified 3 weeks, 3 days ago by  marksquall.
    Attachments:
    You must be logged in to view attached files.

    ________________________________
    "Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20

  • You can use partitioning with aggregate functions to determine whether to count the record in different time periods:

    select 
    distinct ID
    , Today = SUM(CASE WHEN CreatedDate >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) THEN 1 ELSE 0 END) OVER (PARTITION BY ID)
    , [Past 1 Week] = SUM(CASE WHEN CreatedDate >= DATEADD(DAY,-7,CAST(GETDATE() AS DATE)) THEN 1 ELSE 0 END) OVER (PARTITION BY ID)
    , [This Week] = SUM(CASE WHEN CreatedDate >= DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0) THEN 1 ELSE 0 END) OVER (PARTITION BY ID)
    , [This Month] = SUM(CASE WHEN CreatedDate >= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) THEN 1 ELSE 0 END) OVER (PARTITION BY ID)
    , [This Quarter] = SUM(CASE WHEN CreatedDate >= DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()), 0) THEN 1 ELSE 0 END) OVER (PARTITION BY ID)

    from SystemAppUse
    group by ID

    I wasn't sure whether Past 1 Week was the past 7 days or the current week so I have included both in case.

    You should be able to join to the other tables to produce the output you need.  You will have to change the partition functions to align with the other columns (name etc).

  • Jez-448386,

    Hi.  Thank you for this.

    I wonder where would I compute the hours logged here?  The sample output:

    Name                  Today       Past 1 week        This Month            This Quarter

    John Smith             10            20                    30                   50

    The numbers here are actually the hours logged on the application.

    But this is a good starting point.  I am trying to change it to compute hours logged.

    Thank you.

    • This reply was modified 3 weeks, 3 days ago by  marksquall.
    • This reply was modified 3 weeks, 1 day ago by  marksquall.

    ________________________________
    "Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20

  • To find the time difference between to rows then you could use lead or lag.  Here is a rough example

    Use TEMPDB;



    DECLARE @Employee TABLE (
    EmployeeId INT
    ,Name VARCHAR(50)
    )

    INSERT @Employee
    VALUES
    (1,'John Smith')
    ,(2,'John Doe')

    DECLARE @SystemAppUse TABLE (
    ID INT
    ,UserType INT
    ,EmployeeID INT
    ,CreatedDate DATETIME
    )

    INSERT @SystemAppUse
    VALUES
    (1,4,1,'2021-04-14 16:49:12')
    ,(2,1,1,'2021-04-14 16:48:57')
    ,(1,4,1,'2021-04-14 17:32:33')
    ,(2,1,1,'2021-04-14 17:12:44')
    ,(1,4,2,'2021-04-14 15:23:34')
    ,(2,1,2,'2021-04-14 15:12:51');




    WITH CTE AS (
    select *
    , Lead(CreatedDate,1,0) OVER (ORDER BY EmployeeId, UserType) as LoginDateTime
    , CASE
    WHEN UserType <> 4 THEN DATEDIFF(SECOND,CreatedDate,Lead(CreatedDate,1,0) OVER (ORDER BY EmployeeId, CreatedDate) )
    ELSE NULL
    END as LoggedInSeconds
    from @SystemAppUse
    )

    SELECT *
    FROM CTE
    where UserType <> 4

    This will calculate the time from a login event to the next logout event.  It assumes that there cannot be multiple logins before a logout.

  • Jez-448386:

    Hello, Hope you are doing great today.   Thanks for explaining the SQL query.  But unfortunately, this time-in time-out will be used for the log a user spent in a Web application; so every time the user logs in and out, and login again, and out...it will count the total hours spent until the end of shift of that user.  Then I need to group the hours into something similar to the ones example like:

    Name                     Today       Past 1 week        This Month            This Quarter

    John Smith             10                 20                          30                             50

     

    Although the challenge (still) is it doesn't "logout" every time the user closes the web browser, so this is just to assume (for the meantime) that he/she clicks the logout button or when his/her session has expired.

    Thank you again and have a nice day ahead.

    ________________________________
    "Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20

  • One alternate approach could be to use OUTER APPLY and locate the logouts using SELECT TOP(1).  Then you could use ISNULL to assign a default value to cases where there's a login and no logout.  Then the crosstab (or pivot) query could GROUP BY employee and SUM the date differences between login/logout.   Something like this

    /* outer apply to identify logins without logout */
    select sau.*, dt.diff_min
    from @SystemAppUse sau
    outer apply (select top(1) in_sau.CreatedDate
    from @SystemAppUse in_sau
    where sau.EmployeeID=in_sau.EmployeeID
    and sau.CreatedDate<in_sau.CreatedDate
    and in_sau.UserType=4
    order by in_sau.CreatedDate) oa
    outer apply (values (datediff(minute, sau.CreatedDate,
    isnull(oa.CreatedDate, getdate())))) dt(diff_min)
    where sau.UserType<>4;

    /* crosstab query using conditional aggregation */
    select sau.EmployeeID,
    sum(case when sau.CreatedDate >= dateadd(day, -1, getdate()) then dt.diff_min end) past_day,
    sum(case when sau.CreatedDate >= dateadd(month, -1, getdate()) then dt.diff_min end) past_month
    from @SystemAppUse sau
    outer apply (select top(1) in_sau.CreatedDate
    from @SystemAppUse in_sau
    where sau.EmployeeID=in_sau.EmployeeID
    and sau.CreatedDate<in_sau.CreatedDate
    and in_sau.UserType=4
    order by in_sau.CreatedDate) oa
    outer apply (values (datediff(minute, sau.CreatedDate,
    isnull(oa.CreatedDate, getdate())))) dt(diff_min)
    where sau.UserType<>4
    group by sau.EmployeeID;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Steve Collins:

    Hello.  Hope you're doing great today.  Thank you for this sample SQL, but is there a way to combine this "two" SELECT statement into one?  Or can I use UNION for these two SELECT?  And another question, what is dt you mentioned here, and the diff_min?

    Thanks a alot.

    • This reply was modified 2 weeks ago by  marksquall.

    ________________________________
    "Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20

  • Hi there marksquall.  It shouldn't be necessary to combine the queries.  There are 2 queries because your question seemed to contain 2 parts.  Part 1, calculate the date difference in minutes between login and logout.  Part 2, summarize date differences across time periods by employee.  The first query demonstrate calculating the date difference between login and logout.  Then the same query is used as the basis for crosstabulation in the second query.  'dt' is a table alias.  'diff_min' is a column in the 'dt' table.  'diff_min' is the date difference in minutes between login and logout.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

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

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