CODEWITHSUNDEEP
sqlsql-server
Alejandro Ruiz
Alejandro Ruiz

Reputation: 49

Calculate interval time using SQL

I need to calculate the call waiting time (Hold time) during an interval of 30 minutes

HoldTime is given in seconds

Input Data


| Date     | ID    |StartDate | StartTime          |holdtime |
 ---------  ------- ---------- -------------------- ---------
28/12/2022  3110522 28/12/2022  10:03:46.0000000    62
28/12/2022  3110522 28/12/2022  10:36:42.0000000    189
28/12/2022  3110522 28/12/2022  11:06:54.0000000    65
28/12/2022  3110522 28/12/2022  11:11:46.0000000    79
28/12/2022  3110522 28/12/2022  11:19:55.0000000    118
28/12/2022  3110522 28/12/2022  11:38:20.0000000    36
28/12/2022  3110522 28/12/2022  12:13:46.0000000    67
28/12/2022  3110522 28/12/2022  13:45:27.0000000    24
28/12/2022  3110522 28/12/2022  13:52:59.0000000    144
28/12/2022  3110522 28/12/2022  15:02:43.0000000    39
28/12/2022  3110522 28/12/2022  16:00:41.0000000    246
28/12/2022  3110522 28/12/2022  16:54:22.0000000    79
28/12/2022  3110522 28/12/2022  16:59:18.0000000    94
28/12/2022  3110522 28/12/2022  17:29:19.0000000    84
28/12/2022  3110522 28/12/2022  17:54:44.0000000    64

Output Data

|ID    | StartDate |intervalStartTime | intervalStoptTime | holdtime
 ------  ---------  -----------------  ------------------- ----------
3110522 28/12/2022  10:00:00.000       10:30:00.0000000     62
3110522 28/12/2022  10:30:00.000       11:00:00.0000000     189
3110522 28/12/2022  11:00:00.000       11:30:00.0000000     262
3110522 28/12/2022  11:30:00.000       12:00:00.0000000     36
3110522 28/12/2022  12:00:00.000       12:30:00.0000000     67
3110522 28/12/2022  13:30:00.000       14:00:00.0000000     168
3110522 28/12/2022  15:00:00.000       15:30:00.0000000     39
3110522 28/12/2022  16:00:00.000       16:30:00.0000000     246
3110522 28/12/2022  16:30:00.000       17:00:00.0000000     121
3110522 28/12/2022  17:00:00.000       17:30:00.0000000     93
3110522 28/12/2022  17:30:00.000       18:00:00.0000000     107

I am making a function to split data, however I am not getting what I want. I think I'm doing something too complicated to solve it. Using a loop like WHILE is not something I like to use in SQL.

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;

Thanks in advance,

Upvotes: 0

Views: 71

Answers (1)

Squirrel
Squirrel

Reputation: 24763

They key here is to find the timeslot in 30 minutes. The TimeSlot computation is basically to "round down" the time to every 30 minutes. Example : from 10:00 to 10:29 round down to 10:00 etc

select ID, StartDate, 
       intervalStartTime  = TimeSlot, 
       intervalStoptTime  = dateadd(minute, 30, TimeSlot),
       holdtime  = sum(holdtime)
from
(
    select *, 
           TimeSlot = convert(time(0), 
                              dateadd(second, 
                                      convert(int, 
                                              datediff(second, '00:00:00', StartTime) 
                                              / 30.0 / 60.0) * 30 * 60, '00:00'))
    from   InputData
) i
group by  ID, StartDate, TimeSlot

Edit :

CTE numbers is just a tally table. It is used to explode those rows where the holdTime span across multiple time slot

The holdtime (ht) is calculated in below case expression. First condition is where the hold time contains within same time slot. The remaining 3 conditions are to handle when the explode case

ht = case when n = 0 
          and  count(*) over(partition by [Date], [ID], [StartDate], [StartTime]) = 1
          then holdTime
          when n = 0
          then datediff(second, StartTime, dateadd(minute, 30, TimeSlot))
          when n = count(*) over(partition by [Date], [ID], [StartDate], [StartTime]) - 1
          then datediff(second, dateadd(minute, n * 30, TimeSlot), EndTime)
          else 30 * 60
          end

The query

with 
numbers as
(
    select n = 0
    union all
    select n = n + 1
    from   numbers
    where  n < 99
),
cte as
(
    select *
    from   InputData i
           cross apply
           (
               select EndTime  = dateadd(second, holdTime, StartTime),
                      TimeSlot = convert(time(0), 
                                         dateadd(second, 
                                                 convert(int, 
                                                         datediff(second, '00:00:00', StartTime) 
                                                         / 30.0 / 60.0) * 30 * 60, '00:00'))  
           ) t
           cross apply
           (
               select EndTimeSlot = convert(time(0), 
                                         dateadd(second, 
                                                 convert(int, 
                                                         datediff(second, '00:00:00', EndTime) 
                                                         / 30.0 / 60.0) * 30 * 60, '00:00'))  
           ) e
),
cte2 as
(
    select [Date], [ID], [StartDate], [StartTime], [EndTime],
           TimeSlot = dateadd(minute, n * 30, TimeSlot),
           ht = case when n = 0 
                     and  count(*) over(partition by [Date], [ID], [StartDate], [StartTime]) = 1
                     then holdTime
                     when n = 0
                     then datediff(second, StartTime, dateadd(minute, 30, TimeSlot))
                     when n = count(*) over(partition by [Date], [ID], [StartDate], [StartTime]) - 1
                     then datediff(second, dateadd(minute, n * 30, TimeSlot), EndTime)
                     else 30 * 60
                     end
    from   cte i
           inner join numbers n 
                   on n.n >= 0 
                  and n.n <= datediff(minute, TimeSlot, EndTimeSlot) / 30
)
select ID, StartDate, 
       intervalStartTime  = TimeSlot, 
       intervalStoptTime  = dateadd(minute, 30, TimeSlot),
       holdtime  = sum(ht)
from   cte2
group by ID, StartDate, TimeSlot
order by intervalStartTime

db<>fiddle demo

Upvotes: 1

Related Questions