[SQL] Understanding an Insert query that uses derived tables

I'm trying to implement a solution for getting a future work day -- skipping over weekdays and holidays.

There's an excellent solution on this page, the solution by user "T N" Return Date to Exclude Holiday and Weekend

The full setup "T N" created is at here: https://dbfiddle.uk/nnLJhjya

I've gotten his solution working successfully, which is great! Now I'm trying to understand how it works, and I'm stuck on the Insert statement, the one used to populate the Calendar table (see below)

-- Initial range of dates to populate (to be expanded over time)

DECLARE @CalendarFrom DATE = '2024-08-01';
DECLARE @CalendarThru DATE = '2025-01-31';

-- The following are used to accommodate different DATEFIRST settings.

DECLARE @Sat INT = DATEPART(DW, '2000-01-01'); -- Reference Saturday
DECLARE @Sun INT = DATEPART(DW, '2000-01-02'); -- Reference Sunday

INSERT INTO Calendar
SELECT
D.Date,
DT.IsWeekend,
DT.IsHoliday,
WD.IsWorkday,
S.Value + 1 AS DaySequence,
CASE
WHEN WD.IsWorkday = 1
THEN SUM(WD.IsWorkday) OVER(ORDER BY D.Date)
END AS WorkdaySequence,
SUM(IsWorkday) OVER(ORDER BY D.Date) AS ThisOrNextWorkdaySequence,
SUM(IsWorkday) OVER(ORDER BY D.Date) + 1 - WD.IsWorkday AS ThisOrPriorWorkdaySequence,
DATENAME(dw, D.Date) AS DayOfWeek,
TRIM(CONCAT(
CASE WHEN DT.IsWeekend = 1 THEN ' Weekend' END,
CASE WHEN DT.IsHoliday = 1 THEN ' Holiday' END,
CASE WHEN WD.IsWorkday = 1 THEN ' Workday' END)) AS DayType
FROM
GENERATE_SERIES(0, DATEDIFF(day, @CalendarFrom, @CalendarThru)) S
CROSS APPLY
(SELECT DATEADD(day, S.value, @CalendarFrom) AS Date) D
CROSS APPLY
(SELECT
CASE
WHEN DATEPART(dw, D.Date) IN (@Sat, @Sun)
THEN 1
ELSE 0
END AS IsWeekend,
CASE
WHEN EXISTS (SELECT * FROM Holiday H
WHERE H.Date = D.Date)
THEN 1
ELSE 0
END AS IsHoliday) DT
CROSS APPLY
(SELECT
CASE
WHEN DT.IsWeekend = 0 AND IsHoliday = 0
THEN 1
ELSE 0
END AS IsWorkday) WD;


Specifically, the part:

INSERT INTO Calendar
SELECT
D.Date,


In the version I'm running on my server, I've changed the name of the column [Date] in the table [Calendar] to [TheDate]. When I run the above INSERT query (with no changes to it to account for the column name change in the table) it still runs properly!

It doesn't seem to mind that the column name in table Calendar is now called [TheDate].

I'm trying to figure out why, and trying to understanding the D.Date, DT.IsWeekend, etc.

Continue reading...