calculate duration between intervals of 15 minute so i've been trying to find an answer for a problem i have.
i have a client with call information table.
the table look  like this:

and he want me to create a new table that splits every record to hour quarters (0-15,15-30,30-45,45-60) and using the login time and duration of the call calculate for each row how many seconds in each hour quarter he was in the agentsate. so if he was in a talking state for 120 seconds(duration column) and he started talking on logindatetime of 14.3.18 11:44 i should add two columns to the new table for each quarter in the hour and what was the duration for it.
something like this

does anyone have an idea?
i thought about a cursor but i dont understand how to do it and it sounds like its going to have a big impact on performance.
the duration in seconds can be up to a day, not only minutes or seconds.

thanks in advanced.
3 Step approach:


generate the intervals.
match calls against overlapping ranges
figure the duration of each call within that range



create table #calls 
(id int not null identity primary key, 
starttime datetime, 
duration int)

insert into #calls (starttime, duration) values 
('2018-05-14 8:33:00', 45) -- 1 period
,('2018-05-14 8:42:00', 460) -- 2 periods
,('2018-05-14 8:55:00', 3500) -- 5 periods

--Assume table [Number] (N int) with rows 1 to 1 mil. 
;
with Intervals as 
(select top (150000) dateadd(mi, n*15, '2018-01-01') interval_start, 
dateadd(mi, n*15+15, '2018-01-01') interval_end 
from Number order by N)
select * , 

case
--Full 15 min 
when starttime &lt;= interval_start and dateadd(ss, duration, c.starttime) &gt;=  interval_end 
then 15*60
-- partial, overlapping start
when starttime &lt;= interval_start and dateadd(ss, duration, c.starttime) &lt;  interval_end 
then datediff(ss, interval_start, dateadd(ss, duration, c.starttime))
-- partial, overlapping end
when starttime &gt; interval_start and dateadd(ss, duration, c.starttime) &gt;= interval_end 
then datediff(ss, c.starttime, interval_end)
-- partial, wholly contained
when starttime &gt; interval_start and dateadd(ss, duration, c.starttime) &lt; interval_end 
then duration
else null
end  interval_duration

from #calls c inner join Intervals 
on dateadd(ss, duration, c.starttime) &gt;= interval_start 
and c.starttime &lt; interval_end


go

drop table #calls