Slice a row containing time range into multiple rows (tricky sql question)

Question

First of all, I was unable to specify the title to contain my entire problem. My apologies.

My goal is to multiply rows while changing some column values until a certain criterion is met. Yes, awful explanation.

I have:

CREATE TABLE OldTable 
(
    fullname varchar(50) NOT NULL,
    unit varchar(50) NOT NULL,
    code int NOT NULL,
    shift_datetime datetime NOT NULL,
    timespan int NOT NULL
)

INSERT INTO OldTable (fullname, unit, code, shift_datetime, timespan) 
VALUES ('John Smith', 'Heroes', '239', '2020-03-04 13:35:00.000', '55'
'Tom Cruise', 'Heroes', '213', '2020-03-05 09:13:00.000', '8'
'My Mom', 'Heroes', '483', '2020-02-01 08:57:00.000', '16')

yields this table, OldTable:

| fullname   | unit   | code | shift_datetime          | timespan |
+------------+--------+------+-------------------------+----------+
| John Smith | Heroes | 239  | 2020-03-04 13:35:00.000 |      55  |
| Tom Cruise | Heroes | 213  | 2020-03-05 09:13:00.000 |       8  |
| Mom        | Heroes | 483  | 2020-02-01 08:57:00.000 |      16  |

I would like to create this NewTable:

| fullname   | unit   | code | shift_datetime          | timespan |
+------------+--------+------+-------------------------+----------+
| John Smith | Heroes | 239  | 2020-03-04 13:35:00.000 |      15  |
| John Smith | Heroes | 239  | 2020-03-04 13:50:00.000 |      15  |
| John Smith | Heroes | 239  | 2020-03-04 14:05:00.000 |      15  |
| John Smith | Heroes | 239  | 2020-03-04 14:20:00.000 |      10  |
| Tom Cruise | Heroes | 213  | 2020-03-05 09:13:00.000 |       8  |
| Mom        | Heroes | 483  | 2020-02-01 08:57:00.000 |      15  |
| Mom        | Heroes | 483  | 2020-02-01 08:12:00.000 |       1  |

So the problem formulation is rather

if span > 15, then divide into floor(span/15) rows with span = 15 in each row while increasing shift_datetime by 15 miutes for each added row, finally adding the last row where span = span %% 15 and add those span %% 15 minutes to the largest shift_datetime value in that "loop".

If you have any ideas how to 'attack' this problem, I'd appreciate it. I'm not only looking for a solution, but I'm very much looking for advice on how to deal with such issue.

I am able to do this in R through a loop so I'm assuming this can be done by a loop in SQL as well. I would, however, love to hear about other options or ideas.

Answer 1

Sounds like a recursive CTE can help:

with cte as (
      select fullname, unit, code, shift_datetime, 
             (case when timespan > 15 then 15 else timespan end) as timespan,
             timespan as time_remaining, 1 as lev
      from oldtable
      union all
      select fullname, unit, code,
             dateadd(minute, 15, shift_datetime),
             (case when time_remaining > 15 then 15 else time_remaining end) as timespan,
             time_remaining - 15, lev + 1
      from cte
      where time_remaining > 15
     )
select * 
from cte
order by 1, lev;

Here is a db<>fiddle.