Calendar Tables

Calendar tables, or functions, give us the ability to measure or aggregate over time and offer a solution to the problem of a day which is not represented in a result set being omitted from the data set.

Calendar objects are fairly simple to create, in their basic form their little more then a tally or sequence represented in useful columns such as the day of the week, the month of the year or the quarter.

I use a table valued function, which accepts two integers as a parameter which controls the number of years previous and forward of the current date. It returns the following columns:

• Date `DATE` '2023-01-31'
• DateEndTime `DATETIME` '2023-01-31 23:59:59.997' - The last measurable millisecond with a precision of 3 milliseconds.
• Year `INT` `2023`
• MONTH `INT` '1'
• Day `INT` '31'
• Quarter `INT` '1'
• WeekNumber `INT` '5' - The ISO week the date belongs to.
• MonthName `NVARCHAR(20)` 'January' - The month name, per the databases language setting.
• DayName `NVARCHAR(20) 'Tuesday' - The name of the day, per the databases language setting.
• WeekStartDate `DATE` '2023-01-29' - The date on which the week starts based on the current DATEFIRST setting.
• WeekEndDate `DATE` '2023-02-04' - The date on which the week ends based on the current DATEFIRST setting.
• MonthStartDate `DATE` '2023-01-01' 
• MonthEndDate `DATE` '2023-01-31' 
• QuarterStartDate `DATE` '2023-01-01' 
• QuarterEndDate `DATE` '2023-03-31' 
• YearStartDate `DATE` '2023-01-01`
• YearEndDate `DATE` '2023-12-31'
• WeekStartTime `DATETIME` '2023-01-29 00:00:00.000' - The date and time the week starts, based on the current DATEFIRST setting.
• WeekEndTime `DATETIME` '2023-02-04 23:59:59.997' - The date and time the week ends, to the last millisecond, with a precision of 3 milliseconds.
• MonthStartTime `DATETIME` '2023-01-01 00:00:00.000' - The date and time the month starts.
• MonthEndTime `DATETIME` '2023-01-31 23:59:59.997' - The date and time the month ends, to the last millisecond, with a precision of 3 milliseconds.
• QuarterStartTime `DATETIME` - The date and time the quarter starts.
• QuarterEndTime `DATETIME` '2023-01-31 23:59:59.997' - The date and time the quarter ends, to the last millisecond, with a precision of 3 milliseconds.
• IsWeekDay `BIT` '1' - True if the day is a week day (Monday-Friday).

Using the calendar table in a query makes date aggregation trivial. In this example I wanted to collect the number and total amount of invoices for each day in the second half of May 2023.

SELECT c.Date, COUNT(DISTINCT i.InvoiceID) AS Invoices, COALESCE(SUM(ii.Quantity * (ii.Price-ii.Cost)),0) AS InvoiceTotal
  FROM dbo.Calendar(0,0) c
    LEFT OUTER JOIN dbo.Invoices i
	  ON i.InvoiceDateTimeUTC BETWEEN c.Date AND c.DateEndTime
	LEFT OUTER JOIN dbo.InvoiceItems ii
	  ON i.InvoiceID = ii.InvoiceID
 WHERE c.Date BETWEEN '2023-05-15' AND '2023-05-31'
 GROUP BY c.Date
 ORDER BY c.Date

Invoices between May15 and May 31st

Date	Invoices	InvoiceTotal
2023-05-15	137	2792.10
2023-05-16	132	2513.80
2023-05-17	131	2476.60
2023-05-18	129	2611.40
2023-05-19	128	2570.10
2023-05-20	146	3062.80
2023-05-21	120	2454.50
2023-05-22	128	2483.00
2023-05-23	83	1634.40
2023-05-24	0	0.00
2023-05-25	40	717.50
2023-05-26	0	0.00
2023-05-27	0	0.00
2023-05-28	0	0.00
2023-05-29	0	0.00
2023-05-30	0	0.00
2023-05-31	0	0.00

The days which did not have any invoices are still included in the result set, with 0 invoices and a total of 0.

We can also easily use the calendar table to compare one period to another. Here we're comparing the invoice counts between several week numbers in two years, and produce a delta:

;WITH ThisPeriod AS (
SELECT c.WeekNumber, c.WeekStartDate, c.Year, COUNT(i.InvoiceID) AS Invoices
  FROM dbo.Calendar(1,1) c
    LEFT OUTER JOIN Invoices i
	  ON i.InvoiceDateTimeUTC BETWEEN c.Date AND c.DateEndTime
 WHERE c.WeekNumber BETWEEN 20 AND 22
 GROUP BY c.WeekNumber, c.WeekStartDate, c.Year
)

SELECT tp.WeekNumber, tp.WeekStartDate, tp.Invoices, lp.WeekStartDate, lp.Invoices, CAST(CAST(tp.Invoices AS DECIMAL(10,2)) / CAST(lp.Invoices AS DECIMAL(10,2)) AS DECIMAL(3,2))-1 AS DeltaPercent
  FROM ThisPeriod tp
    INNER JOIN ThisPeriod lp
	  ON tp.WeekNumber = lp.WeekNumber
	  AND tp.Year = lp.Year + 1
 WHERE tp.year = 2023;

Invoices in weeks 20, 21 and 22 for 2022 and 2023

WeekNumber	WeekStartDate	Invoices	WeekStartDate	Invoices	DeltaPercent
20	2023-05-14	935	2022-05-08	927	0.01
21	2023-05-21	371	2022-05-15	972	-0.62
22	2023-05-28	0	2022-05-22	937	-1.00

We can also use the calendar table to perform duration calculations. Perhaps we want to know the number of week days in January 2023:

SELECT COUNT(Date) AS Days
  FROM dbo.Calendar(1,1)
 WHERE Month = 1
   AND Year = 2023
   AND IsWeekDay = 1

Week days in January 2023

Days
22

Calendar tables are very useful to have on hand, particularly in reporting type environments. If you'd like to implement the TVF used in these examples you can find the dbo.Calendar.sql in my Azure Dev Ops repo.