TSQL Recipes: Distributing Time by Interval

This should be the last post in 2016. Before the post Merry Christmas and Happy New Year 😊

In the previous post we tried to build a time range by merging time segments. Today let's look at another typical business scenario: distributing time to different slots.

As usual, to make the concept simple, let's look at screenshot below. What I have here is a two column result set, The first column is a date time column which indicates the action start date time, and the 2nd column shows total duration in seconds since the start date time.

Saving the data in such a format definitely is fine. But what if the end user wants to do an analysis by hour? Something like below?

Or even better, flexible output by time interval setting (this time the interval is 15 minutes)?

So this is the topic today. Firstly let's create a few variables

 declare @BeginDateTime datetime = '20161201 00:02:06' -- begin datetime
 declare @Duration int = 17894 -- seconds
 declare @Interval int = 10 --minutes
 declare @Result TABLE 
 (
 BeginDateTime datetime,
 Duration int,
 OutputTimeWindow datetime,
 SecondsLeft int,
 SecondsInWindow int
 )

Hopefully declarations made above make sense to you. If not, basically we need to take the @BeginDateTime as an anchor and then split duration by intervals, and output the result into the result table.

After the declaration, what we need to do is to get some transformed variable: logic is simple when we break down procedures step by step.

declare @BaseDate datetime = CAST(CAST(@BeginDatetime as date) as datetime)  --based date from @BeginDateTime
declare @MinutesToBaseDate  int = DATEDIFF(MINUTE, @BaseDate, @BeginDateTime) --minutes differences between @BeginDatetime and base date
declare @AmountOfIntervals int = @MinutesToBaseDate / @Interval --how many intervals we have

Starts from here, we can begin to build the initial time window

declare @DtWindow datetime = DATEADD(MINUTE, @Interval * @AmountOfIntervals, @BaseDate)
declare @NextDtWindow datetime = dateadd(MINUTE, @interval * (@AmountOfIntervals + 1), @BaseDate)

To make logic simple, I did an initial check to see if the initial time window is sufficient enough to hold the whole duration

IF DATEADD(SECOND, @Duration, @BeginDatetime) < @NextDtWindow 
BEGIN
  insert into @Result(BeginDateTime, Duration , OutputTimeWindow, SecondsLeft, SecondsInWindow)
  select @BeginDatetime, @Duration, DATEADD(MINUTE, @Interval * @AmountOfIntervals, @BaseDate), 0, @Duration
END

Now here is the important part, what if the initial time window is not big enough? What I did is creating a recursive cte and deduct duration by amount of intervals:

;with cte as 
(
select @BeginDatetime as BeginDatetime, @Duration as Duration
    , DATEADD(MINUTE, @Interval * (DATEDIFF(MINUTE, CAST(CAST(@BeginDateTime as date) as datetime), @BeginDateTime) / @Interval), @BaseDate) as DtWindow --base bucket
    , @Duration as SecondsLeft
    , DATEDIFF(SECOND, @BeginDatetime, @NextDtWindow) as SecondsInWindow
union all
select BeginDatetime, Duration
    , DATEADD(MINUTE, @Interval, DtWindow)
    , SecondsLeft - SecondsInWindow
    , IIF(SecondsLeft - SecondsInWindow > @Interval * 60, @Interval * 60, SecondsLeft - SecondsInWindow)
from cte
where SecondsLeft - SecondsInWindow > 0
)

insert into @Result(BeginDateTime, Duration , OutputTimeWindow, SecondsLeft, SecondsInWindow)
select BeginDatetime, Duration, DtWindow, SecondsLeft, SecondsInWindow
from cte

This is all we need. At the end of this post there is a table valued function I created by merging all steps listed above. You can call the function like below:

declare @t table (DT datetime, Duration int)
insert into @t(DT, Duration)
values
('2016-12-01 00:02:06', 17894),
('2016-12-01 05:00:20', 3482),
('2016-12-01 18:00:08', 920),
('2016-12-01 18:15:28', 23)

declare @interval int = 30 --half hour

select x.*
from @t t cross apply dbo.ufnDistributeTimeByInterval(@interval, t.DT, t.Duration) x

Do some tests by changing the @interval variable value. Enjoy it and see you next year.

CREATE function [dbo].[ufnDistributeTimeByInterval](@Interval int, @BeginDateTime datetime, @Duration int)
RETURNS @Result TABLE 
(
 BeginDateTime datetime,
 Duration int,
 OutputTimeWindow datetime,
 SecondsLeft int,
 SecondsInWindow int
)
AS
BEGIN

 declare @BaseDate datetime = CAST(CAST(@BeginDatetime as date) as datetime)  --based date from @BeginDateTime
 declare @MinutesToBaseDate  int = DATEDIFF(MINUTE, @BaseDate, @BeginDateTime) --minutes differences between @BeginDatetime and base date
 declare @AmountOfIntervals int = @MinutesToBaseDate / @Interval --how many intervals we have

 declare @DtWindow datetime = DATEADD(MINUTE, @Interval * @AmountOfIntervals, @BaseDate)
 declare @NextDtWindow datetime = dateadd(MINUTE, @interval * (@AmountOfIntervals + 1), @BaseDate)
 
 IF DATEADD(SECOND, @Duration, @BeginDatetime) < @NextDtWindow 
 BEGIN
  insert into @Result(BeginDateTime, Duration , OutputTimeWindow, SecondsLeft, SecondsInWindow)
  select @BeginDatetime, @Duration, DATEADD(MINUTE, @Interval * @AmountOfIntervals, @BaseDate), 0, @Duration
 END
 ELSE
 BEGIN
  ;with cte as 
  (
   select @BeginDatetime as BeginDatetime, @Duration as Duration
    , DATEADD(MINUTE, @Interval * (DATEDIFF(MINUTE, CAST(CAST(@BeginDateTime as date) as datetime), @BeginDateTime) / @Interval), @BaseDate) as DtWindow --base bucket
    , @Duration as SecondsLeft
    , DATEDIFF(SECOND, @BeginDatetime, @NextDtWindow) as SecondsInWindow
   union all
   select BeginDatetime, Duration
    , DATEADD(MINUTE, @Interval, DtWindow)
    , SecondsLeft - SecondsInWindow
    , IIF(SecondsLeft - SecondsInWindow > @Interval * 60, @Interval * 60, SecondsLeft - SecondsInWindow)
   from cte
   where SecondsLeft - SecondsInWindow > 0
  )

  insert into @Result(BeginDateTime, Duration , OutputTimeWindow, SecondsLeft, SecondsInWindow)
  select BeginDatetime, Duration, DtWindow, SecondsLeft, SecondsInWindow
  from cte

 END --end of if else

 RETURN

END --end of function