Generate a new table from current table-CodePudding

I have a table with only dates for sundays:

dbo.Calendar:

[DatesInCalendar] (DateTime (yyyy-MM-dd))

2022-06-19

2022-06-26

...

Now I want to generate values into another table where I add bookable time slots:

dbo.BookableTimeSlots

[TimeSlots] (DateTime (yyyy-MM-dd hh:mi:ss)) | [Booked] (Bit) | [BookedBy] (NvarChar(10))

So Each value in [TimeSlots] should be based on each value in [DatesInCalendar]:

2022-06-19 10:00:00

2022-06-19 10:30:00

...

2022-06-19 16:00:00

I've used the following query to create my Calendar table, but I'm unable to modify it for my second table BookableTimeSlots.

USE [MyDatabase]
GO

declare @startDate date,@enddate date
set @startDate='2022-06-01'
set @enddate='2025-06-01'

while @startDate<=@enddate
begin
if(DATENAME(dw,@startDate)='Sunday')
INSERT INTO [dbo].[Calendar]
           ([DatesInCalendar])
     VALUES
           (convert(date,@startDate,103))
set @startDate=DATEADD(DD,1,@startDate) 
end
GO

Does anyone have a suggestion for how I can populate my second table based on dates from my first table and adding a timestamp with 30 min intervals (10 am to 4p)?

[Booked] = 0

[BookedBy] = Null

CodePudding user response：

Create a table, or use a derived table or cte, with 1 row per wanted timeslot in a single day. Cross join these rows to your calendar table, and using dateadd to suit, that query will generate all the needed timeslot rows and these can be inserted into your target table. e.g.

with cte (mysunday) as (
    select cast('20220605' as date) as mysunday
    union all
    select dateadd(day,7,cte.mysunday)
    from cte
    where cte.mysunday < '20220731'
    )
, tally as (
    select digit from ( values(1),(2),(3),(4),(5),(6),(7),(8) ) d (digit)
    )
select 
    mysunday, dateadd(hour,tally.digit,cast(cte.mysunday as datetime)) as slot, tally.digit
from cte
cross join tally

mysunday   | slot                    | digit
:--------- | :---------------------- | ----:
2022-06-05 | 2022-06-05 01:00:00.000 |     1
2022-06-05 | 2022-06-05 02:00:00.000 |     2
2022-06-05 | 2022-06-05 03:00:00.000 |     3
2022-06-05 | 2022-06-05 04:00:00.000 |     4
2022-06-05 | 2022-06-05 05:00:00.000 |     5
2022-06-05 | 2022-06-05 06:00:00.000 |     6
2022-06-05 | 2022-06-05 07:00:00.000 |     7
2022-06-05 | 2022-06-05 08:00:00.000 |     8
2022-06-12 | 2022-06-12 01:00:00.000 |     1
2022-06-12 | 2022-06-12 02:00:00.000 |     2
2022-06-12 | 2022-06-12 03:00:00.000 |     3
2022-06-12 | 2022-06-12 04:00:00.000 |     4
2022-06-12 | 2022-06-12 05:00:00.000 |     5
2022-06-12 | 2022-06-12 06:00:00.000 |     6
2022-06-12 | 2022-06-12 07:00:00.000 |     7
2022-06-12 | 2022-06-12 08:00:00.000 |     8
etc.

db<>fiddle here