Creating a permanent calendar table (also known as a date dimension) is a best practice for production systems that involve reporting, scheduling, or time-based analytics. It simplifies logic, improves performance, and makes queries more readable.

🧱 What Is a Calendar Table?

A calendar table is a user-defined table with one row per date and columns for various date-related attributes.

Example Schema

CREATE TABLE dbo.Calendar (
    CalendarDate     DATE PRIMARY KEY,
    Year             INT,
    Month            INT,
    Day              INT,
    Weekday          INT, -- 1=Sunday, 7=Saturday
    DayOfWeekName    VARCHAR(10),
    IsWeekend        BIT,
    IsHoliday        BIT,
    YearMonth        CHAR(6), -- e.g. '202504'
    Quarter          INT,
    DayOfYear        INT,
    WeekOfYear       INT,
    FiscalYear       INT,
    FiscalPeriod     INT
    -- Add more fields as needed
);

✅ Benefits

🧠 Example Use Case

SELECT 
    c.YearMonth,
    COUNT(*) AS WorkdayCount
FROM dbo.Calendar c
WHERE c.CalendarDate BETWEEN '2024-07-01' AND '2025-06-30'
  AND c.IsWeekend = 0
  AND c.IsHoliday = 0
GROUP BY c.YearMonth
ORDER BY c.YearMonth;

🔧 How to Populate It

Here’s how to generate dates and populate the calendar:

DECLARE @StartDate DATE = '2020-01-01';
DECLARE @EndDate   DATE = '2030-12-31';

WITH DateList AS (
    SELECT @StartDate AS CalendarDate
    UNION ALL
    SELECT DATEADD(DAY, 1, CalendarDate)
    FROM DateList
    WHERE CalendarDate < @EndDate
)
INSERT INTO dbo.Calendar (
    CalendarDate, Year, Month, Day, Weekday, DayOfWeekName,
    IsWeekend, YearMonth, Quarter, DayOfYear, WeekOfYear
)
SELECT 
    CalendarDate,
    YEAR(CalendarDate),
    MONTH(CalendarDate),
    DAY(CalendarDate),
    DATEPART(WEEKDAY, CalendarDate),
    DATENAME(WEEKDAY, CalendarDate),
    CASE WHEN DATENAME(WEEKDAY, CalendarDate) IN ('Saturday', 'Sunday') THEN 1 ELSE 0 END,
    FORMAT(CalendarDate, 'yyyyMM'),
    DATEPART(QUARTER, CalendarDate),
    DATEPART(DAYOFYEAR, CalendarDate),
    DATEPART(WEEK, CalendarDate)
FROM DateList
OPTION (MAXRECURSION 0);

🧩 Add Holiday Info

Assuming you maintain a dbo.Holidays table:

UPDATE c
SET c.IsHoliday = 1
FROM dbo.Calendar c
JOIN dbo.Holidays h ON c.CalendarDate = h.HolidayDate;

🏁 Final Thoughts

Creating and using a permanent calendar table ensures:

• Better performance
• Reusable logic
• Cleaner, more maintainable SQL

💡 Tip: Build it once, use it everywhere!

SELECT 
    t1.TransactionType,
    t1.TransactionDate,
    YEAR(t1.TransactionDate) AS TransactionYear,
    MONTH(t1.TransactionDate) AS TransactionMonth,
    LEAD(t1.TransactionDate, 1, DATEADD(day, 1, EOMONTH(t1.TransactionDate)))
            OVER (PARTITION BY t1.TransactionType,YEAR(t1.TransactionDate),MONTH(t1.TransactionDate) ORDER BY t1.TransactionDate) AS NextTransactionDate,
    DATEDIFF(day, t1.TransactionDate, 
        LEAD(t1.TransactionDate, 1, DATEADD(day, 1, EOMONTH(t1.TransactionDate)))
            OVER (PARTITION BY t1.TransactionType,YEAR(t1.TransactionDate),MONTH(t1.TransactionDate) ORDER BY t1.TransactionDate)) AS DifferenceInDays
FROM Transactions t1
GROUP BY 
    t1.TransactionType,
    t1.TransactionDate,
    YEAR(t1.TransactionDate),
    MONTH(t1.TransactionDate)
ORDER BY 
    t1.TransactionType,
    t1.TransactionDate;

LEAD (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )  provides access to a row at a specified physical offset which follows the current row. It allows you to query more than one row in a SQL statement without having to join the table to itself

The  EOMONTH function in SQL is used to get the last day of the month for a specified date

DATEADD(day, 1, EOMONTH(t1.TransactionDate)) 找出月底再+1得到下月初