Splitting time + duration into intervals in t-sql

Question

Hi,

Does anyone know of a simple method for solving this?

I have a table which consists of start times for events and the associated durations. I need to be able to split the event durations into thirty minute intervals. So for example if an event starts at 10:45:00 and the duration is 00:17:00 then the returned set should allocate 15 minutes to the 10:30:00 interval and 00:02:00 minutes to the 11:00:00 interval.

I'm sure I can figure out a clumsy approach but would like something a little simpler. This must come up quite often I'd imagine but Google is being unhelpful today.

Thanks,

Steve

Answer 1

You could create a lookup table with just the times (over 24 hours), and join to that table. You would need to rebase the date to that used in the lookup. Then perform a datediff on the upper and lower intervals to work out their durations. Each middle interval would be 30 minutes.

create table #interval_lookup (
  from_date datetime,
  to_date datetime
)

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

while @time < '2 Jan 1900'
  begin
    insert into #interval_lookup values (@time, dateadd(minute, 30, @time))
    set @time = dateadd(minute, 30, @time)
  end

declare @search_from datetime
declare @search_to datetime

set @search_from = '10:45:00'
set @search_to = dateadd(minute, 17, @search_from) 

select
  from_date as interval,
  case
    when from_date <= @search_from and @search_from < to_date and from_date <= @search_to and @search_to < to_date then datediff(minute, @search_from, @search_to)
    when from_date <= @search_from and @search_from < to_date then datediff(minute, @search_from, to_date)
    when from_date <= @search_to and @search_to < to_date then datediff(minute, from_date, @search_to)
    else 30
  end as duration
from
  #interval_lookup
where
  to_date > @search_from
  and from_date <= @search_to

Answer 2

Create TVF that splits single event:

ALTER FUNCTION dbo.TVF_TimeRange_Split_To_Grid
(
 @eventStartTime datetime
 , @eventDurationMins float
 , @intervalMins int
)
RETURNS @retTable table
(
 intervalStartTime datetime
 ,intervalEndTime datetime
 ,eventDurationInIntervalMins float
)
AS
BEGIN

 declare @eventMinuteOfDay int
 set @eventMinuteOfDay = datepart(hour,@eventStartTime)*60+datepart(minute,@eventStartTime)

 declare @intervalStartMinute int
 set @intervalStartMinute = @eventMinuteOfDay - @eventMinuteOfDay % @intervalMins

 declare @intervalStartTime datetime
 set @intervalStartTime = dateadd(minute,@intervalStartMinute,cast(floor(cast(@eventStartTime as float)) as datetime))

 declare @intervalEndTime datetime
 set @intervalEndTime = dateadd(minute,@intervalMins,@intervalStartTime)

 declare @eventDurationInIntervalMins float

 while (@eventDurationMins>0)
 begin

  set @eventDurationInIntervalMins = cast(@intervalEndTime-@eventStartTime as float)*24*60
  if @eventDurationMins<@eventDurationInIntervalMins 
   set @eventDurationInIntervalMins = @eventDurationMins

  insert into @retTable
  select @intervalStartTime,@intervalEndTime,@eventDurationInIntervalMins

  set @eventDurationMins = @eventDurationMins - @eventDurationInIntervalMins
  set @eventStartTime = @intervalEndTime

  set @intervalStartTime = @intervalEndTime
  set @intervalEndTime = dateadd(minute,@intervalMins,@intervalEndTime)
 end

 RETURN 
END
GO

Test:

select getdate()
select * from dbo.TVF_TimeRange_Split_To_Grid(getdate(),23,30)

Test Result:

2008-10-31 11:28:12.377

intervalStartTime       intervalEndTime         eventDurationInIntervalMins
----------------------- ----------------------- ---------------------------
2008-10-31 11:00:00.000 2008-10-31 11:30:00.000 1,79372222222222
2008-10-31 11:30:00.000 2008-10-31 12:00:00.000 21,2062777777778

Sample usage:

select input.eventName, result.* from
(
 select 
  'first' as eventName
  ,cast('2008-10-03 10:45' as datetime) as startTime
  ,17 as durationMins
 union all
 select 
  'second' as eventName
  ,cast('2008-10-05 11:00' as datetime) as startTime
  ,17 as durationMins
 union all
 select 
  'third' as eventName
  ,cast('2008-10-05 12:00' as datetime) as startTime
  ,100 as durationMins
) input
cross apply dbo.TVF_TimeRange_Split_To_Grid(input.startTime,input.durationMins,30) result

Sample usage result:

eventName intervalStartTime       intervalEndTime         eventDurationInIntervalMins
--------- ----------------------- ----------------------- ---------------------------
first     2008-10-03 10:30:00.000 2008-10-03 11:00:00.000 15
first     2008-10-03 11:00:00.000 2008-10-03 11:30:00.000 2
second    2008-10-05 11:00:00.000 2008-10-05 11:30:00.000 17
third     2008-10-05 12:00:00.000 2008-10-05 12:30:00.000 30
third     2008-10-05 12:30:00.000 2008-10-05 13:00:00.000 30
third     2008-10-05 13:00:00.000 2008-10-05 13:30:00.000 30
third     2008-10-05 13:30:00.000 2008-10-05 14:00:00.000 10

(7 row(s) affected)

Answer 3

Thanks for the replies. I'll try them out on Monday.

Answer 4

That works perfectly. Many thanks.