November 04, 2023

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
DateInvoicesInvoiceTotal
2023-05-15137 2792.10
2023-05-16132 2513.80
2023-05-17131 2476.60
2023-05-18129 2611.40
2023-05-19128 2570.10
2023-05-20146 3062.80
2023-05-21120 2454.50
2023-05-22128 2483.00
2023-05-2383 1634.40
2023-05-240 0.00
2023-05-2540 717.50
2023-05-260 0.00
2023-05-270 0.00
2023-05-280 0.00
2023-05-290 0.00
2023-05-300 0.00
2023-05-310 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
WeekNumberWeekStartDateInvoicesWeekStartDateInvoicesDeltaPercent
202023-05-14935 2022-05-08927 0.01
212023-05-21371 2022-05-15972 -0.62
222023-05-280 2022-05-22937 -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.



No comments:

Post a Comment