SQL - Break a start/end time into 15 minute records

Question

I've got a record set that consists of a start and end time in two separate fields:

id - Int
startTime - DateTime
endTime - DateTime

I'd like to find a way to query a record and return it as X records based on the number of 15 minute intervals found between the start and end times.

For example, let's say I have a record like this:

id, StartTime, EndTime
1, 1/1/2010 8:28 AM, 1/1/2010 8:47 AM

I would return 3 records, the first would represent the 8:15 interval, #2 for the 8:30 interval and then a 3rd for the 8:45 interval.

I realize this could be done using logic in an sproc, but we are trying to remain db neutral as we support multiple database engines.

Answer 1

Why don't you do the processing in a server-side language? It would be much easier and would definitely allow you to be db-neutral.

Answer 2

There appear to be two basic approaches.
1. Iteratively caclulate each interval (either a loop or recursion)
2. Use a lookup table

As some the implentation of functions (for recursive calculation) and even the defintion for Loops will vary, the most generic would appear to be a lookup table. It is also likely to be very quick.

Answer 3

I agree with Keith that this is probably better done in an app. server and the cross join to the lookup table will get expensive for a large source table, but just for fun I worked up a quick sample.

   declare @QuarterHours table (
        QuarterHour time
    )

    declare @x time
    set @x = '00:00'

    insert into @QuarterHours
        (QuarterHour)
        values 
        (@x)
    set @x = DATEADD(minute, 15, @x)

    while @x <> '00:00' begin
        insert into @QuarterHours
            (QuarterHour)
            values 
            (@x)
        set @x = DATEADD(minute, 15, @x)
    end /* while */

    declare @test table (
        id int,
        starttime datetime,
        endtime datetime
    )

    insert into @test
        (id, starttime, endtime)
        values
        (1, '2010-01-01 08:28', '2010-01-01 08:47')

    select t.id, q.QuarterHour
        from @test t
            cross join @QuarterHours q
        where q.QuarterHour between cast(t.starttime as time) and cast(t.endtime as time)