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
Change is inevitable... Change for the better is not.