Select number of items for an 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

General SQL Server Forums

New to SQL Server Programming

Select number of items for an interval

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2011-03-11 : 11:29:20

I have a Requests table:

-----------------------------------------
Requests
-----------------------------------------
RequestID | DateRequested
-----------------------------------------

I'm trying to create some statistics out of this table, I want to see how many requests came in every 15 minutes for a certain date, and get something like this:

TimeStamp | Count
00:00 | 2
00:15 | 6
00:30 | 4
00:45 | 3
01:00 | 8
01:15 | 7

Which would mean that between 12:00 AM and 12:15 AM we got 2 requests, between 12:15 AM and 12:30 AM we got 6 requests, and so on.

Let's just say that the certain date is yesterday, 03/10/2011.

Can someone help me out with this? I'm really bad at Dates.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-11 : 12:04:45

You need to be able to group the DateRequested into 15 minute groups - something like this:

dateadd(mi,15*(datediff(mi,0,DateRequested)/15),0)

That is simply rounding down the time to the nearest 15 minute interval using integer division. Then you could write your query using that as

select
   dateadd(mi,15*(datediff(mi,0,DateRequested)/15),0)
   count(*),
from
   YourTable
group by
   dateadd(mi,15*(datediff(mi,0,DateRequested)/15),0)

Apples
Posting Yak Master

146 Posts

Posted - 2011-03-11 : 12:36:39

That's fantastic, thank you so much. Works perfectly.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-11 : 13:31:56

You are very welcome Apples, glad it worked for you/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-11 : 18:24:31

We now have DATE and TIME data types that we can use for time slots.

First, build a tabe of your time slots. Load it with time ranges at a higher precision than your actual source data. Start at '00:00:00' and not end at '24:00:00' since that is '00:00:00' the next day.

CREATE TABLE TimeSlots
(timeslot_nbr INTEGER NOT NULL PRIMARY KEY,
start_time TIME(2) NOT NULL,
end_time TIME(2) NOT NULL,
CHECK (start_time < end_time)):

INSERT INTO TimeSlots
VALUES (1, '00:00:00.00', '00:15:00.00'),
(2, '00:15:00.01', '00:30:00.00'),
..
(96, '23:45:00.01', '23:59:59.99');

Let's assume that we are doing gauge readings for the raw data.

CREATE TABLE GaugeReadings
(reading_datetime DATETIME NOT NULL PRIMARY KEY,
reading_amt INTEGER NOT NULL);

Finally, classify each data point into a time slot and total them

SELECT reading_date, timeslot_nbr, gauge_tot
FROM (SELECT CAST (reading_datetime AS DATE),
T.timeslot_nbr, SUM(reading_amt)
FROM TimeSlots AS T, GaugeReadings
WHERE CAST(reading_datetime AS TIME)
AS X (reading_date, timeslot_nbr, gauge_tot)
WHERE X.reading_date BETWEEN X.start_time AND X.end_time
GROUP BY X.reading_date, X.timeslot_nbr;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-12 : 08:00:18

Joe, usually I look at the designs you propose in these forums and admire the elegance and (for lack of a better term) the "neatness" of those designs. In this case however, may be because I am not following your thought process, the creation of TimeSlots table seems to me to be restricting the flexibility.

If I understood Apple's requirement, it is that s/he has/will have at various points in time which s/he wants to save in a database. From there, s/he wants to generate reports - one of which happens to be the 15 minute interval report. There may be other reports - for example a 20-minute interval report etc. If that indeed is the case, is it better not to have the TimeSlot table at all, and generate the reports as I had suggested in my original response?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources