CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
TheJoeIaut
TheJoeIaut

Reputation: 1532

Group by Time Interval

I need to Group my Table into 15 minutes Intervals. I can do that with:

select dateadd(minute, datediff(minute, 0, ts) / 15 * 15, 0), sum (goodpieces) 
from StationCount 
Group by dateadd(minute, datediff(minute, 0, ts) / 15 * 15, 0)

But to display the returned data in a chart i need to insert also the intervals which don't have any data and aren't currently appearing in my select statement. How do i insert these?

Upvotes: 0

Views: 2842

Answers (2)

Ariel
Ariel

Reputation: 26753

Create a table with every possible timestamp in 15 minute increments, and do a LEFT JOIN from it to your query above.

SELECT * FROM timestamps LEFT JOIN (SELECT dateadd......) ON timestamps.timestamp = StationCount.ts

If you know your chart always covers a 24 hour period, you only need to create a table with the numbers 0-95, then for each entry add it to the start time of your chart.

SELECT *
  FROM (SELECT dateadd(minute, <starttime>, number*15) timestamp FROM numbers) timestamps LEFT JOIN
       (SELECT dateadd......) ON timestamps.timestamp = StationCount.ts

Upvotes: 1

Johan
Johan

Reputation: 1192

Something like this might help you.

declare @startDate datetime
        ,@endDate datetime

set @startDate = '2011-10-25'
set @endDate = '2011-10-26'

;with fifteenMinutes
as
(
    select  dateadd(minute, datediff(minute, 0, @startDate) / 15 * 15, 0) as q
    UNION ALL
    select  dateadd(minute, 15, q)
    from fifteenMinutes
    where q < @endDate
)

select * from fifteenMinutes

Upvotes: 1

Related Questions