SQL Server Group By Durations

Question

After some searching, I was able to get results using this query.

SELECT 
    DATEDIFF(HOUR, LOGINTIME, LOGOUTTIME) 
FROM 
    [SESSION]
GROUP BY 
    DATEPART(YEAR, DATEDIFF(HOUR,LOGINTIME,LOGOUTTIME)),
    DATEPART(MONTH, DATEDIFF(HOUR,LOGINTIME,LOGOUTTIME)),
    DATEPART(DAY, DATEDIFF(HOUR,LOGINTIME,LOGOUTTIME)),
    DATEPART(HOUR, DATEDIFF(HOUR,LOGINTIME,LOGOUTTIME)),
    (DATEPART(MINUTE, DATEDIFF(HOUR,LOGINTIME,LOGOUTTIME)) / 10),
    LoginTime, LogoutTime

It is a table having sessions of user login and logout times. What I want exactly is to calculate how many users were having an hour two or more. I want to get the count.

A sample result from this query was.

0
0
0
1
1
1
2
4
17
20

Answer 1

I got to do it using this query after adding "SessionDuration" as a computed column with "LogoutTime-LoginTime".

SELECT COUNT(DATEPART(HOUR,SessionDuration)) AS [Count],
       DATEPART(HOUR,SessionDuration) AS SessionDuration 
FROM [SESSION]
GROUP BY DATEPART(HOUR,SessionDuration)