Sample table
id dept ctime
100 A 09:15:12.0000000
200 B 08:00:00.0000000
300 A 09:00:00.0000000
400 B 08:50:00.0000000
expect to see
countid dept xtime
2 A 08:00:00 08:50:00
2 B 09:00:00 09:15:12
My query
SELECT count(id) countid, dept,
DATEADD(MI, -(DATEPART(MI, ctime) % 5), cTime) xtime
FROM table1
GROUP BY dept, DATEADD(MI, -(DATEPART(MI, ctime) % 5), cTime)
Output. But not sure why not group by hours
countid dept xtime
1 B 08:00:00.0000000
1 B 08:50:00.0000000
1 A 09:00:00.0000000
1 A 09:15:12.0000000
Advertisement
Answer
Based on this comment, it seems you just want to aggregate to the hours:
Try to group by each hours like between 8:00:00 to 8:59:00 and 9:00:00 to 9:59:00 and by dept.
If so, why not just use DATEPART
with MIN
and MAX
?
SELECT COUNT(ID) AS CountID,
Dept,
MIN(ctime) AS MinTime,
Max(ctrime) AS MaxTime
FROM dbo.YourTable
GROUP BY Dept,
DATEPART(HOUR, ctime);