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
