Split date range into intervals and count seconds in each interval in t-sql

Question

I have a query I am trying to make on a table that has multiple entries for people with a start time and an end time e.g.:

name,startime,endtime
('richard','2010-04-21 08:01:15','2010-04-21 08:06:15'),
('bill','2010-04-21 08:07:45','2010-04-21 08:11:15')

What I need to do is create a report showing the seconds of each entry within each 5 minute interval e.g.

name,time,seconds
------------------
richard,8:00,225
richard,8:05,75
bill,8:05,135
bill,8:10,75

So the query must create these intervals and then count the seconds for each record to show the total seconds in that interval. Any help would be appreciated.

Answer 1

Following script should get you started.

A few notes of warning though

• it does not work in its current form if starttime and endtime are not the same day
• there are no checks in place wether starttime is less than endtime

SQL Script

DECLARE @People TABLE (
  Name VARCHAR(32)
  , StartTime DATETIME
  , EndTime DATETIME
)

DECLARE @MinHour INTEGER
DECLARE @MaxHour INTEGER
DECLARE @Times TABLE (
  Hour INTEGER
  , Minute INTEGER
)

INSERT INTO @People
SELECT           'richard', '2010-04-21 08:01:15', '2010-04-21 08:06:15'
UNION ALL SELECT 'bill'   , '2010-04-21 08:07:45', '2010-04-21 08:11:15'


SELECT  @MinHour = MIN(DATEPART(hh, StartTime))
        , @MaxHour = MAX(DATEPART(hh, EndTime))
FROM    @People

WHILE @MinHour < @MaxHour + 1
BEGIN
  INSERT INTO @Times 
  SELECT @MinHour, 0
  UNION ALL SELECT @MinHour, 5
  UNION ALL SELECT @MinHour, 10
  UNION ALL SELECT @MinHour, 15
  UNION ALL SELECT @MinHour, 20
  UNION ALL SELECT @MinHour, 25
  UNION ALL SELECT @MinHour, 30
  UNION ALL SELECT @MinHour, 35
  UNION ALL SELECT @MinHour, 40
  UNION ALL SELECT @MinHour, 45
  UNION ALL SELECT @MinHour, 50
  UNION ALL SELECT @MinHour, 55
  SET @MinHour = @MinHour + 1
END

SELECT  p.Name
        , t.Hour
        , t.Minute
        , CASE WHEN DATEPART(mi, p.EndTime) - DATEPART(mi, p.EndTime) % 5  = t.Minute 
          THEN 60 * (DATEPART(mi, p.EndTime) % 5) + DATEPART(ss, p.EndTime)
          ELSE 300 - 60 * (DATEPART(mi, p.StartTime) % 5) - DATEPART(ss, p.StartTime)
          END
FROM    @People p
        INNER JOIN @Times t ON DATEPART(hh, p.StartTime) = t.Hour
                               AND DATEPART(hh, p.EndTime) = t.Hour
                               AND DATEPART(mi, p.StartTime) - DATEPART(mi, p.StartTime) % 5 <= t.Minute
                               AND DATEPART(mi, p.EndTime) - DATEPART(mi, p.EndTime) % 5 >= t.Minute
ORDER BY 
        p.Name

Answer 2

Here's an iterative solution that will work over any time period where the start time is before the end time. You didn't specify whether you wanted to keep the date component for reporting time intervals in different days at 8am on a different line or whether you wanted to group them. It should be quite easy to discard the date component by converting the time_bucket column in my solution to a varchar then substring, group by that varchar then sum the seconds in that group.

If you're doing analysis by time buckets then you should probably have a time bucket dimension table, similar to what Lieven proposes in his solution. That solves your cardinality issues. Without that you'd have to do something like this:

create table #results
( name varchar(20) not null, time_bucket datetime not null, seconds int not null )

declare @name varchar(20), @startTime datetime, @endTime datetime, @timeBucket datetime, @secondsInBucket int
declare dataCur cursor for select * from source_data
open dataCur

fetch next from dataCur into @name, @startTime, @endTime
while @@fetch_status = 0
begin
    set @timeBucket = convert(datetime, convert(varchar(14), @startTime, 120) + convert(varchar(2), (datepart(mi, @startTime) / 5) * 5), 120)

    while @timeBucket < @endTime
    begin
        set @secondsInBucket = case
            when @timeBucket < @startTime then datediff(ss, @startTime, dateadd(mi, 5, @timeBucket))
            when @endTime < dateadd(mi, 5, @timeBucket) then datediff(ss, @timeBucket, @endTime)
            else 300
        end

        insert into #results values (@name, @timeBucket, @secondsInBucket)
        set @timeBucket = dateadd(mi, 5, @timeBucket)
    end

    fetch next from dataCur into @name, @startTime, @endTime
end

close dataCur
deallocate dataCur

select * from #results