How Works Recursive CTE in SQL Server?
A CTE Common Table Expression is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. A recursive CTE in SQL Server is a special kind of CTE that refers to itself. It’s useful for tasks like generating sequences like dates or numbers or traversing hierarchical data, e.g., org charts, folder structures etc.
A recursive CTE in SQL Server consists of:
- Anchor member – the starting point.
- Recursive member – repeatedly executed until a condition is met.
Recursive CTE in SQL Server
-- Prevent regional settings from interfering with date interpretation
SET DATEFIRST 7, -- Set Sunday as the first day of the week
DATEFORMAT mdy, -- Set date format to Month-Day-Year
LANGUAGE US_ENGLISH;-- Use US English for language settings
-- Declare start and cutoff dates
DECLARE @StartDate date = '20100101'; -- Start date: Jan 1, 2010
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 3, @StartDate));
-- Cutoff date: one day before Jan 1, 2013 (i.e., Dec 31, 2012)
-- Recursive CTE to generate a sequence of numbers from 0 to number of days between StartDate and CutoffDate
WITH seq(n) AS
(
SELECT 0 -- Anchor member: start with 0
UNION ALL
SELECT n + 1 -- Recursive member: increment n
FROM seq
WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
-- Stop when n reaches the number of days between StartDate and CutoffDate
)
SELECT n FROM seq -- Select the generated sequence
ORDER BY n -- Order the result
OPTION (MAXRECURSION 0); -- Allow unlimited recursion (default is 100)
Recursive CTE in SQL Server.
Data table for BI
In business intelligence (BI) and data warehousing, a date dimension table is a foundational component that enables powerful time-based analysis. This SQL script demonstrates how to dynamically generate a comprehensive date dimension using a recursive Common Table Expression (CTE). Starting from a defined StartDate, it builds a sequence of dates up to a CutoffDate, enriching each date with attributes such as day name, month, quarter, week number, and weekend flag. This structure supports flexible filtering, grouping, and visualization in BI tools like Power BI, Tableau, or SQL Server Reporting Services (SSRS), and ensures consistent date logic across reports and dashboards.
Recursive CTE in SQL Server
- Translate the generated numbers into actual dates.
- Create a date dimension table suitable for use in BI tools like Power BI or Tableau.
Recursive CTE in SQL Server
-- Regional settings
SET DATEFIRST 7,
DATEFORMAT mdy,
LANGUAGE US_ENGLISH;
-- Define date range
DECLARE @StartDate date = '20100101';
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 3, @StartDate));
-- Recursive CTE to generate date dimension
WITH seq(n) AS
(
SELECT 0
UNION ALL
SELECT n + 1
FROM seq
WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
DateDimension AS
(
SELECT
DATEADD(DAY, n, @StartDate) AS Date,
DATENAME(WEEKDAY, DATEADD(DAY, n, @StartDate)) AS DayName,
DATEPART(WEEKDAY, DATEADD(DAY, n, @StartDate)) AS DayOfWeek,
DATEPART(DAY, DATEADD(DAY, n, @StartDate)) AS Day,
DATEPART(MONTH, DATEADD(DAY, n, @StartDate)) AS Month,
DATENAME(MONTH, DATEADD(DAY, n, @StartDate)) AS MonthName,
DATEPART(YEAR, DATEADD(DAY, n, @StartDate)) AS Year,
DATEPART(QUARTER, DATEADD(DAY, n, @StartDate)) AS Quarter,
DATEPART(WEEK, DATEADD(DAY, n, @StartDate)) AS WeekOfYear,
CASE
WHEN DATEPART(WEEKDAY, DATEADD(DAY, n, @StartDate)) IN (1, 7) THEN 1
ELSE 0
END AS IsWeekend
FROM seq
)
SELECT * FROM DateDimension
ORDER BY Date
OPTION (MAXRECURSION 0);
Trad more here and How To Set The First Day of Week DATEFIRST
Next Steps
- Add fiscal year columns
- Add holiday flags (if you provide a list)
- Localize day/month names to Polish if needed
Fiscal Year Columns
Recursive CTE in SQL Server
-- Assuming fiscal year starts in April
SELECT
Date,
CASE
WHEN MONTH(Date) >= 4 THEN YEAR(Date)
ELSE YEAR(Date) - 1
END AS FiscalYear,
CASE
WHEN MONTH(Date) BETWEEN 4 AND 6 THEN 1
WHEN MONTH(Date) BETWEEN 7 AND 9 THEN 2
WHEN MONTH(Date) BETWEEN 10 AND 12 THEN 3
ELSE 4
END AS FiscalQuarter
FROM DateDimension
Holiday Flags
It requires a holiday list. Assuming you have a table called HolidayList with a column HolidayDate.
Recursive CTE in SQL Server
SELECT
d.*,
CASE
WHEN h.HolidayDate IS NOT NULL THEN 1
ELSE 0
END AS IsHoliday
FROM DateDimension d
LEFT JOIN HolidayList h ON d.Date = h.HolidayDate
Localize Day/Month Names to Polish
SELECT
Date,
CASE DATENAME(WEEKDAY, Date)
WHEN 'Monday' THEN 'Poniedziałek'
WHEN 'Tuesday' THEN 'Wtorek'
WHEN 'Wednesday' THEN 'Środa'
WHEN 'Thursday' THEN 'Czwartek'
WHEN 'Friday' THEN 'Piątek'
WHEN 'Saturday' THEN 'Sobota'
WHEN 'Sunday' THEN 'Niedziela'
END AS DayNamePL,
CASE DATENAME(MONTH, Date)
WHEN 'January' THEN 'Styczeń'
WHEN 'February' THEN 'Luty'
WHEN 'March' THEN 'Marzec'
WHEN 'April' THEN 'Kwiecień'
WHEN 'May' THEN 'Maj'
WHEN 'June' THEN 'Czerwiec'
WHEN 'July' THEN 'Lipiec'
WHEN 'August' THEN 'Sierpień'
WHEN 'September' THEN 'Wrzesień'
WHEN 'October' THEN 'Październik'
WHEN 'November' THEN 'Listopad'
WHEN 'December' THEN 'Grudzień'
END AS MonthNamePL
FROM DateDimension
This extended SQL script builds a robust date dimension table tailored for business intelligence applications. Starting with a recursive CTE to generate a sequence of dates, it enriches each entry with key calendar attributes such as day names, month names, quarters, and weekend flags. Additional enhancements include fiscal year logic, holiday indicators (based on a provided list), and localization of day and month names to Polish. These features ensure the table supports flexible, localized, and context-aware time-based analysis in tools like Power BI, Tableau, or SQL Server Reporting Services.
One Comment
Comments are closed.