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
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;
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
Days |
---|
22 |
No comments:
Post a Comment