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

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.

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.

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.

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)