To generate a date range in SQL Server, number the days between a start date and an end date and add each number to the start. This post shows a recursive CTE, its limit of 100 recursions, GENERATE_SERIES, a version for older servers, and how to handle times and missing days.
SQL SERVER – CTE can be Updated
Users often confuse a CTE with a temp table, but a CTE works like a view, and a CTE can be updated just like one. Matthew Spieth’s tip, with my notes.
Loan Amortization Schedules With a Recursive CTE
Why does the last loan payment leave a few cents behind? A recursive CTE, a rounding rule and a final reconciliation fix it.
SQL SERVER – Does Use of CTE Change the Order of Join in Inner Join
A reader said he uses a CTE to control join order. Does a CTE change the order of join in an inner join? Here is what I found.
Cohort Retention Tables in T-SQL: From Raw Events to a Grid
Build a retention grid from raw orders with five rows you can check by eye. Includes the DISTINCT rule and why unfinished months show NULL.
Walking Foreign Key Chains to Find a Safe Delete Order
Map foreign key chains from a parent table with a recursive catalog query, then list deepest children first and flag cascade rules.
CTE Referenced Twice: Why the Query Runs It Twice
Writing a CTE name two times can mean doing the work two times. Two small demos show when a temp table helps and when it only adds cost.