SQL Select and group by time periods (frequency of events)?

I have a table of time and date stamps (which represent an event) gathered thoughout the week and I need to output the freqeuncy of the events at fixed time periods which would be variable. ( i.e. 1 min or 1 hour or 12 hours.) to a new table for graphing. 0 needs to be logged in case of no event. As a VB solution I can loop through and count the events per time period which will work fine but i'm sure there is a better way. As an attempted SQL solution I can group on a digit in the timedate string. [stamp]





SELECT Left([Stamp],16) AS [Time], Count(GaV.Stamp) AS Records


FROM GaV


GROUP BY Left([Stamp],16);





This misses 'no event' periods as there is nothing to group on.





I was looking for a more elegant way. Any Ideas any one?

SQL Select and group by time periods (frequency of events)?
I would use recursive SQL or a procedure with a loop to create rows in the graphing table, putting 0 in the count for each group. Then run an update to change the count for each result from the GROUP BY query.





The following query will give you the count for each group, like your query, but will allow you to specify any time period size from 1 second to 1 day, using :hours :minutes :seconds as the time period.





with


events as (select stamp, date(stamp) as day, (hour(stamp) * 60 + minute(stamp)) * 60 + second(stamp) as seconds from gav),


groups as (select day, seconds / ((:hours * 60 + :minutes) * 60 + :seconds) as segment from events)


select day, segment, count(*) from groups group by day, segment;
Reply:It's difficult to answer the way your question is worded. But assuming you have legitimate timestamps in the stamp column, you can get a count of events between two time periods by using the following:





CREATE PROCEDURE GetEventCount


@starttime CHAR(16),


@endtime CHAR(16)





AS





SELECT COUNT(stamp) AS totalevents


FROM GaV


WHERE LEFT(stamp, 16) %26gt;= @starttime


AND LEFT(stamp, 16) %26lt;= @endtime





GO





This stored procedure will return the count of all events between the two times you call.





You simply procide the starttime and endtime parameters to the stored procedure, then execute it; if there are no events, the totalevents return column will contain a zero; otherwise, you get the count.

teeth pictures