I have a database where user events are recorded quite frequently. I'd like to be able to get a count of the 'good' events that happen in each 5 second period. Unfortunately I don't know how to display and group by a time range.
Here is the query I would like to change:
SELECT count(*), clientTime
FROM dbo.V_COMBINED
WHERE (sessionId = '122b') AND (type = N'sys_goodaction') AND (paraName = 'value')
GROUP BY clientTime
It returns records like:
1 | 2006-02-16 23:21:05.250
1 | 2006-02-16 23:21:05.267
1 | 2006-02-16 23:21:06.470
I'd like it to return records like:
5 | 2006-02-16 23:21:06 - 23:21:10
3 | 2006-02-16 23:21:11 - 23:21:15
4 | 2006-02-16 23:21:16 - 23:21:20
Anyone know how I could do this? Is it even possible?
ThanksYes It is possible.
Post your table structure, sample data and expected result.|||There are many ways to do this, but I'd use:SELECT count(*), DateAdd(second, -DatePart(second, clientTime) % 5
, DateAdd(ms, -DatePart(ms, clientTime), clientTime))
FROM dbo.V_COMBINED
WHERE (sessionId = '122b')
AND (type = N'sys_goodaction')
AND (paraName = 'value')
GROUP BY DateAdd(second, -DatePart(second, clientTime) % 5
, DateAdd(ms, -DatePart(ms, clientTime), clientTime))-PatP
No comments:
Post a Comment