I have a table t_times that looks like this

I need to create a table using the values in the t_times to create t_slots

In essence for every value specified in the number_of_slots field I need to:

• Create a new row in the target table
• Add the corresponding time slot (start and end) using the duration taking into account the slot_duration field's value. I.e. for a value of 6 I need to add 6 rows with ten minute increments each that uses start_time as the starting value

I can do it with a cursor but it seems a very roundabout way of doing this. Can someone please point me in the SQL direction? Thanks!!!

CodePudding user response：

So the main idea here is to use classic approach with number table. It is quite common to have such table as a persistent one, so the one won't need to generate it on run time. It's a common tool for analytical stuff as yours. Second is JOIN it with the source one on the right predicate.

use tempdb
go

drop table if exists src
go

create table src (
    start_time  time
    ,End_time   time
    ,Number_of_slots int    
    ,Slot_Duration int
)
go

insert into src values
('08:00',   '09:00',    6,  10)
,('09:00',  '09:30',    1,  30)
go

;with nums as (
    select row_number() over(order by (select null)) as n
    from sys.all_columns
)

select start_time, dateadd(MINUTE, n*Slot_Duration, start_time) as end_time
    , Slot_Duration as Duration
from src 
join nums on src.Number_of_slots >= nums.n
order by start_time, end_time