Group data into 15 minute intervals

Question

Group data into 15 minute intervals

Viewing 2 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

@Dwain,

Yes. Build them as you need them. The exception to the rule might be where you need to consider holidays or do certain bits of date magic like finding the next day that's 5 business days away. You could, however, even build those very quickly. As with all else, "It Depends". There are advantages to both methods. That's why I have both a Tally Table and a Tally function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

Alan.B (4/8/2015)
You said 15 minute intervals but your example shows 30 min. To do 15 change @interval to 15. You could also dynamically calculate the start and end dates. This should be enough to get you what you need though.
DECLARE
@interval int = 30,
@start datetime = '2015-04-08 01:00:00.000',
@finish datetime = '2015-04-08 12:30:00.000';
WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
iTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E1 a, E1 b, E1 c),
dateRanges(s,f) AS
(
SELECT DATEADD(MINUTE, (N-1)*@interval, @start), DATEADD(MINUTE, N*@interval, @start)
FROM iTally
)
SELECT s, IRevenue = ISNULL(SUM(IRevenue),0), TRevenue = ISNULL(SUM(TRevenue),0)
FROM dateRanges dr
LEFT JOIN #HalfHourlyIntervals hhi ON OrderDate >= s AND OrderDate < f
WHERE s <= @finish
GROUP BY s
ORDER BY s;-- included for presentation; this is not needed to get the correct answer and will slow you down
Edit: Included the changes that Kevin suggested. Added a variable for the time and interval.

Really nice, Alan. The only problem is that it results in 24 scans of the source table (1 scan for each count of the cte) for a full Cartesian product of 504 internally generated rows. I don't know if the optimizer would take the same route for a larger number of rows but it's something that I'd really watch for. Perhaps an index on the source table might help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)