Query over time interval with fixed group interval

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query over time interval with fixed group interval

Author

Topic

blake
Starting Member

29 Posts

Posted - 2005-11-02 : 22:04:46

I have a table with events logged to them and my client application is grabbing the number of events over a certain time period to graph them. The table looks like:

create table events(
event_id int not null references event_names(event_id),
event_date datetime not null default CURRENT_TIMESTAMP
)

So I'd like to have a running count of the events over the last n minutes, where n can be 5, 60, 240, etc. That's easy to do but what I'd like is to group the results so that I always get 50 records returned.

So if I'm requesting the count of events over the last 50 minutes, each record would represent the count of events which happened over a 1 minute time slice.

Any pointers?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-04 : 05:17:16

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

blake
Starting Member

29 Posts

Posted - 2005-11-04 : 21:45:43

quote:
Originally posted by madhivanan

Post some sample data and the result you want

Sample data:


event_id  event_date
--------  -----------------------
1         2005-10-01 00:00:00.012
2         2005-10-01 00:00:00.038
3         2005-10-01 00:00:00.152
4         2005-10-01 00:00:00.349
5         2005-10-01 00:00:00.685
6         2005-10-01 00:00:00.723
7         2005-10-01 00:00:00.891
8         2005-10-01 00:00:00.954
9         2005-10-01 00:00:01.056
10        2005-10-01 00:00:01.230
11        2005-10-01 00:00:01.507
...

1011       2005-10-01 00:00:59.019
1012       2005-10-01 00:00:59.238
1013       2005-10-01 00:00:59.934

So let's say I want to plot 60 seconds worth of data. In order to do this, I'll need to take those 1013 records and slice them up such that I'm using n equal time slices, and for convenience let's assume that n=50.

So I'll have 50 bars on a bar chart, and 1 minute divided by 50 means that each bar is effectively a grouping of 60/50 or 1.2 seconds.

So the results for 50 slices over 60 seconds would look like:


slice_number  event_start_date          count
------------  -----------------------   -----
1             2005-10-01 00:00:00.000   10
2             2005-10-01 00:00:01.200   6
3             2005-10-01 00:00:02.400   11
4             2005-10-01 00:00:03.600   9
...
50            2005-10-01 00:00:58.800   5

etc. Does that make sense?

The number of slices should be a parameter, and the time range should also be a parameter.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources