By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T(
EmployeeID INT,
[Date] Date,
Login DATETIME,
Logout DATETIME
);
INSERT INTO T VALUES (123,'10/31/2018','10/31/2018 8:30','10/31/2018 10:35');
INSERT INTO T VALUES (234,'10/31/2018','10/31/2018 9:30','10/31/2018 11:37');
INSERT INTO T VALUES (345,'10/31/2018','10/31/2018 9:00','10/31/2018 11:09');
;WITH CTE AS (
SELECT [Date],Login,Logout
FROM T
UNION ALL
SELECT [Date],DATEADD(mi, 30, Login) ,Logout
FROM CTE
WHERE DATEADD(mi, 30, Login) < Logout
),CelanderCte AS(
SELECT *, CAST(Login AS TIME) LoginTitme
FROM CTE
)
SELECT convert(char(10), [Date], 101) 'DATE',
COUNT(CASE WHEN LoginTitme >= '8:30' AND LoginTitme < '9:00' THEN 1 END) '8:30:00 AM',
COUNT(CASE WHEN LoginTitme >= '9:00' AND LoginTitme < '9:30' THEN 1 END) '9:00:00 AM',
COUNT(CASE WHEN LoginTitme >= '9:30' AND LoginTitme < '10:00' THEN 1 END) '9:30:00 AM',
COUNT(CASE WHEN LoginTitme >= '10:00' AND LoginTitme < '10:30' THEN 1 END) '10:00:00 AM',
COUNT(CASE WHEN LoginTitme >= '10:30' AND LoginTitme <'11:00' THEN 1 END) '10:30:00 AM',
COUNT(CASE WHEN LoginTitme >= '11:00' AND LoginTitme <'11:30' THEN 1 END) '11:00:00 AM'
FROM CelanderCte
GROUP BY CONVERT(char(10), [Date],101)
| DATE | 8:30:00 AM | 9:00:00 AM | 9:30:00 AM | 10:00:00 AM | 10:30:00 AM | 11:00:00 AM |
|---|---|---|---|---|---|---|
| 10/31/2018 | 1 | 2 | 3 | 3 | 3 | 2 |
Warning: Null value is eliminated by an aggregate or other SET operation.