SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

6 rows affected

-- using recursive CTE to generate a numbers/tally table
-- Or use GENERATE_SERIES (SQL Server 2022+) on the fly
-- For performance, use a permanant numbers table
with numbers as
(
  select n = 0
  union all
  select n = n + 1
  from   numbers 
  where  n < 99 --assumed max 99. Change accordingly
),
cte as
(
select *   
from   tbl t
       cross apply
       (
           -- Start & End Time in 15 mins block HH:00, HH:15, HH:30, HH:45
           select StartB = dateadd(minute, datediff(minute, 0, StartTime) / 15 * 15, 0),
                  EndB   = dateadd(minute, ceiling(datediff(minute, 0, EndTime) / 15.0) * 15, 0)
       ) blk
       cross apply
       (
           -- calculate no of 15 mins block
           select blocks = datediff(minute, StartB, EndB) / 15
       ) b
       inner join numbers n on  n.n >= 1
                            and n.n <= b.blocks
       cross apply
       (
           select BlockStart = dateadd(minute, (n.n - 1) * 15, StartB),
                  BLockEnd   = dateadd(minute, n.n * 15, StartB)
       ) bt
       cross apply
       (
           select r = case when b.blocks = 1
                           then 1.0
                           when n.n = 1
                           then datediff(minute, StartTime, BlockEnd) * 1.0 / datediff(minute, StartTime, EndTime)
                           when n.n = b.blocks
                           then datediff(minute, BlockStart, EndTime) * 1.0 / datediff(minute, StartTime, EndTime)
                           else 15.0 / datediff(minute, StartTime, EndTime)
                           end
       ) r
) 
select *, 
       ProductiveSeconds = convert(decimal(10,2), TotalDurationSeconds * r)
from   cte c
order by c.TechnicianId, c.StartTime, c.n
-- option (maxrecursion 0)   -- unmask for more than 100 recursion

TechnicianID	StartTime	EndTime	TotalDurationSeconds	StartB	EndB	blocks	n	BlockStart	BLockEnd	r	ProductiveSeconds
1	2023-10-01 05:00:00.000	2023-10-01 05:10:00.000	600	2023-10-01 05:00:00.000	2023-10-01 05:15:00.000	1	1	2023-10-01 05:00:00.000	2023-10-01 05:15:00.000	1.000000000000	600.00
1	2023-10-01 05:10:00.000	2023-10-01 05:25:00.000	900	2023-10-01 05:00:00.000	2023-10-01 05:30:00.000	2	1	2023-10-01 05:00:00.000	2023-10-01 05:15:00.000	0.333333333333	300.00
1	2023-10-01 05:10:00.000	2023-10-01 05:25:00.000	900	2023-10-01 05:00:00.000	2023-10-01 05:30:00.000	2	2	2023-10-01 05:15:00.000	2023-10-01 05:30:00.000	0.666666666666	600.00
1	2023-10-01 06:00:00.000	2023-10-01 06:45:00.000	2700	2023-10-01 06:00:00.000	2023-10-01 06:45:00.000	3	1	2023-10-01 06:00:00.000	2023-10-01 06:15:00.000	0.333333333333	900.00
1	2023-10-01 06:00:00.000	2023-10-01 06:45:00.000	2700	2023-10-01 06:00:00.000	2023-10-01 06:45:00.000	3	2	2023-10-01 06:15:00.000	2023-10-01 06:30:00.000	0.333333333333	900.00
1	2023-10-01 06:00:00.000	2023-10-01 06:45:00.000	2700	2023-10-01 06:00:00.000	2023-10-01 06:45:00.000	3	3	2023-10-01 06:30:00.000	2023-10-01 06:45:00.000	0.333333333333	900.00
2	2023-10-01 05:05:00.000	2023-10-01 05:20:00.000	900	2023-10-01 05:00:00.000	2023-10-01 05:30:00.000	2	1	2023-10-01 05:00:00.000	2023-10-01 05:15:00.000	0.666666666666	600.00
2	2023-10-01 05:05:00.000	2023-10-01 05:20:00.000	900	2023-10-01 05:00:00.000	2023-10-01 05:30:00.000	2	2	2023-10-01 05:15:00.000	2023-10-01 05:30:00.000	0.333333333333	300.00
2	2023-10-01 05:30:00.000	2023-10-01 05:35:00.000	300	2023-10-01 05:30:00.000	2023-10-01 05:45:00.000	1	1	2023-10-01 05:30:00.000	2023-10-01 05:45:00.000	1.000000000000	300.00
2	2023-10-01 07:00:00.000	2023-10-01 07:50:00.000	3000	2023-10-01 07:00:00.000	2023-10-01 08:00:00.000	4	1	2023-10-01 07:00:00.000	2023-10-01 07:15:00.000	0.300000000000	900.00
2	2023-10-01 07:00:00.000	2023-10-01 07:50:00.000	3000	2023-10-01 07:00:00.000	2023-10-01 08:00:00.000	4	2	2023-10-01 07:15:00.000	2023-10-01 07:30:00.000	0.300000000000	900.00
2	2023-10-01 07:00:00.000	2023-10-01 07:50:00.000	3000	2023-10-01 07:00:00.000	2023-10-01 08:00:00.000	4	3	2023-10-01 07:30:00.000	2023-10-01 07:45:00.000	0.300000000000	900.00
2	2023-10-01 07:00:00.000	2023-10-01 07:50:00.000	3000	2023-10-01 07:00:00.000	2023-10-01 08:00:00.000	4	4	2023-10-01 07:45:00.000	2023-10-01 08:00:00.000	0.100000000000	300.00