April 20, 2021 at 8:39 am
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
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
April 20, 2021 at 11:04 am
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).
April 20, 2021 at 1:07 pm
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.
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
April 22, 2021 at 11:36 am
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.
April 29, 2021 at 4:38 am
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
April 29, 2021 at 2:41 pm
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
April 30, 2021 at 4:10 pm
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.
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
April 30, 2021 at 7:24 pm
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply