SQL Query help required

Transact-SQL
1

Hi Team,

I have a table with 3 columns - Timestamp, UserID and Acitivity.

Activity can hold the values like - Login , Logout, Task held etc.

I want to write a query to count the no.of users who are logged in into the system on hourly basis.

SELECT MAX(timestamp) from table where Activity = 'Login' group by UserID.

This will give the latest entry for each user.

But as per my requirement, I need to get the count of users on hourly basis.

If a user is logged in at 10:00 am and is active till 12:30 am, he should be counted for 10:00 am , 11:00 am, 12:00 am in all these 3 hours.

Do you see is this possible with only these 3 columns available ?

Regards,
Abdul

2

HI,

I used a utility table/cte - for generating the list of hours. ( cteHourInterval)
Then, for every userID, take the last login and the last logout to see the hour interval

It is easy for use, if you have some sample data and the desired output.

;WITH cteHourInterval AS
( SELECT CAST('08:00 am' AS Time) AS HInterval 
	UNION ALL SELECT CAST('09:00 am' AS Time) 
	UNION ALL SELECT CAST('10:00 am' AS Time) 
	UNION ALL SELECT CAST('11:00 am' AS Time) 
	UNION ALL SELECT CAST('12:00 pm' AS Time) 
	UNION ALL SELECT CAST('13:00 pm' AS Time) 
	UNION ALL SELECT CAST('14:00 pm' AS Time) 
	UNION ALL SELECT CAST('15:00 pm' AS Time) 
	UNION ALL SELECT CAST('16:00 pm' AS Time) 
	UNION ALL SELECT CAST('17:00 pm' AS Time) 
)
,cteSource AS
( SELECT  CAST('10:00 am'  AS Time ) AS TS, 1 AS UserID,'Login' AS Activity 
  UNION ALL SELECT  CAST('12:30 pm'  AS Time ), 1,'Logout'
  UNION ALL SELECT  CAST('14:00 pm'  AS Time ), 2,'Login'
  UNION ALL SELECT  CAST('16:30 pm'  AS Time ), 2,'Logout'
)


SELECT
	HI.HInterval
	,CJ.UserID
FROM
	cteHourInterval AS HI
	CROSS JOIN
	(SELECT
		UserID
	 FROM	
		cteSource AS S
	GROUP BY
		S.UserID) AS CJ		
	CROSS APPLY
	(SELECT TOP(1)
		TS
	 FROM
		cteSource AS S
	 WHERe
		CJ.UserID = S.UserID
		AND S.Activity = 'Login'
	 ORDER BY 
		TS DESC) AS CA_IN
	CROSS APPLY
	(SELECT TOP(1)
		TS
	 FROM
		cteSource AS S
	 WHERE
		CJ.UserID = S.UserID
		AND S.Activity = 'Logout'
	 ORDER BY 
		TS DESC) AS CA_OUT
WHERE
	HI.HInterval>=CA_IN.TS
	AND HI.HInterval <= CA_OUT.TS

output for this:

HInterval        UserID
10:00:00.0000000 1
11:00:00.0000000 1
12:00:00.0000000 1
14:00:00.0000000 2
15:00:00.0000000 2
16:00:00.0000000 2
3

Thanks for the information. I will try out this option and get back to you.