Day 12: Temporal tables & time-based queries

A. Lesson overview

Activity breakdown

Business scenario. Operations wants to know how quickly online orders are shipped, measured in working days (excluding weekends and bank holidays). HR’s tenure report gives the wrong answer for some people. Merchandising needs to answer “what was the list price of this tent in mid-2023?” and “which prices have changed since the start of 2023?” — a job for a temporal table that keeps price history automatically.

B. Theory explained clearly

Part 1 — Date and time types

RetailLab stores business dates as date. Staging load times are datetime2(0).

Literals. '2025-12-31' is always read as 31 December 2025 when converted to date, datetime2 or datetimeoffset. For datetime, the dashed format can be misread under some language settings (e.g. British/French date order); '20251231' (no separators) is safe for every type. Never write '31/12/2025' or '12/31/2025' — their meaning depends on the session’s DATEFORMAT.

Current time functions

The course uses the fixed reference date 2025-12-31 instead of these functions, so results don’t change over time.

Part 2 — Date functions and their traps

Trap 1 — DATEDIFF counts boundaries, not elapsed units

DATEDIFF(YEAR, '2025-12-31', '2026-01-01') is 1 — one year boundary was crossed — although only one day passed. DATEDIFF(MONTH, '2025-01-31', '2025-02-01') is 1 as well. For “completed” units, adjust:

-- Completed months between @Start and @End
DATEDIFF(MONTH, @Start, @End) - CASE WHEN DAY(@End) < DAY(@Start) THEN 1 ELSE 0 END

Divide completed months by 12 for completed years. (The rule treats e.g. 31 Jan → 28 Feb as not quite a month; adapt it if the business counts month-ends differently.)

Trap 2 — Weekday numbers depend on DATEFIRST

DATEPART(WEEKDAY, d) returns 1–7, but which day is 1 depends on the session’s SET DATEFIRST (US English default: Sunday = 1). DATENAME(WEEKDAY, d) depends on SET LANGUAGE. Code that must behave the same for everyone should use a calculation that ignores both, e.g. DATEDIFF(DAY, '19000101', d) % 7 + 1 gives 1 = Monday … 7 = Sunday (1 January 1900 was a Monday) — or simply look the value up in a calendar table.

Trap 3 — DATEADD with months clamps

DATEADD(MONTH, 1, '2025-01-31') is 28 February; adding another month gives 28 March, not 31 March. To step through month ends, compute each from a fixed start: EOMONTH('2025-01-31', n).

Trap 4 — Functions on columns in WHERE

WHERE YEAR(OrderDate) = 2025 is correct but prevents SQL Server from seeking an index on OrderDate (it must compute YEAR for every row). WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01' returns the same rows and can use an index. Day 24 calls this sargability.

FORMAT and CONVERT

FORMAT(d, 'dd MMM yyyy', 'en-GB') produces display strings using .NET formatting; it is convenient but noticeably slower than CONVERT on large result sets. CONVERT(char(8), d, 112) gives 20251231; style 23 gives 2025-12-31; style 103 gives 31/12/2025. Format dates in the presentation layer where possible.

Part 3 — Date ranges and calendar tables

Half-open ranges

For any column that can contain a time, filter a period as start inclusive, end exclusive:

WHERE CreatedAt >= '2025-12-01' AND CreatedAt < '2026-01-01'

BETWEEN '2025-12-01' AND '2025-12-31' on a datetime2 column misses everything after midnight on 31 December, because '2025-12-31' means 2025-12-31 00:00:00. Half-open ranges work for date and date-time types alike, and adjacent periods never overlap.

Calendar tables

A calendar table has one row per date with useful attributes pre-computed: year, quarter, month name, ISO week, weekday, weekend and bank-holiday flags, fiscal periods. Benefits:

• No gaps: joining facts to the calendar with a LEFT JOIN guarantees one row per day or month, even when nothing happened (Day 6’s moving-average caveat).
• Business rules in data: working days, bank holidays and fiscal calendars live in one place instead of in every query.
• Settings-independent: names and weekday numbers are stored, not computed per session.
• Fast: a few thousand rows; easy to index.

Today’s setup script builds Lab.Calendar for 2024–2026 with England and Wales bank holidays.

Part 4 — System-versioned temporal tables

What they do

A system-versioned temporal table is a pair of tables managed by SQL Server:

• the current table, which you query and modify normally, and
• a history table, to which SQL Server automatically copies the old version of every row that is updated or deleted.

Each row carries two datetime2 period columns, here ValidFrom and ValidTo, maintained by SQL Server. They record the UTC time at which the database recorded the change (system time). You cannot write to them directly while versioning is on.

-- How such a table is normally declared (for reference; the setup script uses a migration method)
CREATE TABLE dbo.Example
(
    ID        int           NOT NULL PRIMARY KEY,
    Value     decimal(10,2) NOT NULL,
    ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo   datetime2(0) GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ExampleHistory));

Requirements: a primary key on the current table; exactly one PERIOD FOR SYSTEM_TIME with two non-nullable datetime2 columns; a history table with the same columns and types and no constraints. The “end” value of current rows is the maximum for the chosen precision (9999-12-31 23:59:59 for datetime2(0)).

Querying history: FOR SYSTEM_TIME

SELECT * FROM Lab.ProductListPrice FOR SYSTEM_TIME AS OF '2023-06-30';

SQL Server combines the current and history tables for you. AS OF returns one version per key (or none, if the row did not exist then).

System time versus business time

Temporal tables record when the database changed, in UTC. That is ideal for auditing (“who could have seen which price when?”) and recovering accidentally changed data. It is not the same as business validity — “this price applies from 1 February” — if the change is entered before or after the date it takes effect. For effective-dated business data you model your own EffectiveFrom/EffectiveTo columns (Day 30’s slowly changing dimension does exactly that).

In Lab.ProductListPrice the history was loaded with chosen past dates so that the lesson is reproducible; any change you make today is stamped with the current UTC time.

Other things to know

• Altering or dropping a temporal table requires care: you must set SYSTEM_VERSIONING = OFF before dropping it (the setup script shows how).
• History grows with every update. Plan a retention policy (HISTORY_RETENTION_PERIOD) for busy tables.
• TRUNCATE TABLE is not allowed on a system-versioned table.
• Temporal tables work the same way in SQL Server, Azure SQL Database and Azure SQL Managed Instance; check the documentation before assuming the same in other platforms.

C. Dataset and starting state

Run the setup script

1. Open scripts/lessons/day12_calendar_and_temporal.sql (download it) in SSMS (save it next to your setup scripts, e.g. C:\RetailLab\scripts\lessons\).
2. Press F5.
3. The last result grid should show: 1096, 760, 24, 29, 10, SYSTEM_VERSIONED_TEMPORAL_TABLE.

The script is safe to re-run: it drops and re-creates only these three Lab objects. Re-running it is also how you restore the price history after today’s update exercises.

What the script creates

Validation. Run this after the script; expected 3, 29, 3.

USE RetailLab;
GO

SELECT  (SELECT COUNT(*) FROM Lab.ProductListPrice FOR SYSTEM_TIME ALL WHERE ProductID = 1) AS Product1Versions,
        (SELECT COUNT(*) FROM Lab.ProductListPrice)                                          AS CurrentRows,
        (SELECT COUNT(*) FROM Lab.Calendar WHERE CalendarDate = '2025-12-25' AND IsWorkingDay = 0)
      + (SELECT COUNT(*) FROM Lab.Calendar WHERE CalendarDate = '2025-12-26' AND IsWorkingDay = 0)
      + (SELECT COUNT(*) FROM Lab.Calendar WHERE CalendarDate = '2025-12-27' AND IsWeekend = 1)  AS ChristmasChecks;

D. Worked examples

Worked example 1 — Date functions on one date

DECLARE @d date = '2025-11-20';

SELECT  YEAR(@d)                         AS Yr,
        DATEPART(QUARTER, @d)            AS Qtr,
        DATEPART(ISO_WEEK, @d)           AS IsoWeek,
        DATEADD(DAY, 30, @d)             AS Plus30Days,
        DATEADD(MONTH, -1, @d)           AS MinusOneMonth,
        EOMONTH(@d)                      AS MonthEnd,
        EOMONTH(@d, 1)                   AS NextMonthEnd,
        DATEFROMPARTS(YEAR(@d), 1, 1)    AS YearStart,
        DATETRUNC(MONTH, @d)             AS MonthStart,
        DATEDIFF(DAY, @d, '2025-12-31')  AS DaysToYearEnd;

Expected result: 2025, 4, 47, 2025-12-20, 2025-10-20, 2025-11-30, 2025-12-31, 2025-01-01, 2025-11-01, 41.

Worked example 2 — DATEDIFF counts boundaries

SELECT  DATEDIFF(YEAR,  '2025-12-31', '2026-01-01') AS YearsCrossed,     -- 1
        DATEDIFF(MONTH, '2025-01-31', '2025-02-01') AS MonthsCrossed,    -- 1
        DATEDIFF(DAY,   '2025-01-31', '2025-02-01') AS DaysBetween,      -- 1
        DATEADD(MONTH, 1, CAST('2025-01-31' AS date)) AS OneMonthLater,  -- 2025-02-28
        DATEADD(MONTH, 1, DATEADD(MONTH, 1, CAST('2025-01-31' AS date))) AS TwoStepsLater; -- 2025-03-28

Expected result: 1, 1, 1, 2025-02-28, 2025-03-28. The last value shows month clamping accumulating: two steps of one month from 31 January land on 28 March.

Worked example 3 — BETWEEN misses the last day for date-time values

WITH Events AS
(
    SELECT CAST(t AS datetime2(0)) AS EventTime
    FROM  (VALUES ('2025-12-30 09:15:00'), ('2025-12-31 00:00:00'),
                  ('2025-12-31 17:45:00'), ('2026-01-01 00:00:00')) AS v(t)
)
SELECT  (SELECT COUNT(*) FROM Events WHERE EventTime BETWEEN '2025-12-30' AND '2025-12-31')        AS WithBetween,
        (SELECT COUNT(*) FROM Events WHERE EventTime >= '2025-12-30' AND EventTime < '2026-01-01')  AS HalfOpen;

Expected result: 2 and 3. BETWEEN excluded the 17:45 event on 31 December; the half-open range includes it and correctly excludes 1 January.

Worked example 4 — Orders per weekday, fairly (calendar table)

SELECT  c.IsoDayOfWeek,
        c.DayName,
        COUNT(DISTINCT c.CalendarDate)                                     AS DaysInYear,
        COUNT(o.OrderID)                                                   AS Orders,
        CAST(1.0 * COUNT(o.OrderID) / COUNT(DISTINCT c.CalendarDate) AS decimal(4,2)) AS OrdersPerDay
FROM    Lab.Calendar AS c
LEFT JOIN Sales.SalesOrder AS o
       ON  o.OrderDate   = c.CalendarDate
       AND o.OrderStatus <> 'Cancelled'
WHERE   c.CalendarYear = 2025
GROUP BY c.IsoDayOfWeek, c.DayName
ORDER BY c.IsoDayOfWeek;

Expected result

Why the calendar matters: 2025 has 53 Wednesdays and 52 of every other day. Dividing by the number of days avoids favouring Wednesday. The LEFT JOIN from the calendar counts days with no orders as zero-order days — the cancelled-order filter is in ON (Day 8).

Worked example 5 — Temporal table: current, as of, and full history

-- Current version (an ordinary query)
SELECT ProductID, ListPrice, ValidFrom, ValidTo
FROM   Lab.ProductListPrice
WHERE  ProductID IN (1, 22)
ORDER BY ProductID;

-- The price list as it stood on 30 June 2023
SELECT ProductID, ListPrice, ValidFrom, ValidTo
FROM   Lab.ProductListPrice FOR SYSTEM_TIME AS OF '2023-06-30'
WHERE  ProductID IN (1, 2, 9, 22, 30)
ORDER BY ProductID;

-- Every version of product 1
SELECT ProductID, ListPrice, ValidFrom, ValidTo
FROM   Lab.ProductListPrice FOR SYSTEM_TIME ALL
WHERE  ProductID = 1
ORDER BY ValidFrom;

Expected result

1. Current: product 1 at 189.00 (since 2024-01-01), product 22 at 299.00 (since 2023-11-15). ValidTo is 9999-12-31 23:59:59.
2. As of 2023-06-30: product 1 179.00, product 2 329.00, product 9 5.99, product 22 319.00, product 30 599.00. The whole price list that day has 26 products (SELECT COUNT(*) … AS OF '2023-06-30').
3. All versions of product 1: 169.00 (2022-03-01 → 2023-03-01), 179.00 (2023-03-01 → 2024-01-01), 189.00 (2024-01-01 → 9999-12-31 23:59:59).

Worked example 6 — Change a price and watch history being written

DECLARE @Before datetime2(0) = SYSUTCDATETIME();

UPDATE Lab.ProductListPrice
SET    ListPrice = 72.00
WHERE  ProductID = 26;                       -- BPK-001 Daypack, was 69.00

SELECT ProductID, ListPrice, ValidFrom, ValidTo
FROM   Lab.ProductListPrice FOR SYSTEM_TIME ALL
WHERE  ProductID = 26
ORDER BY ValidFrom;

SELECT ListPrice AS PriceJustBeforeUpdate
FROM   Lab.ProductListPrice FOR SYSTEM_TIME AS OF @Before
WHERE  ProductID = 26;

Expected result: two versions of product 26 — 69.00 valid from 2022-03-01 until the moment you ran the update (in UTC), and 72.00 valid from that moment until 9999-12-31 23:59:59. The AS OF @Before query returns 69.00. Your exact timestamps will differ from anyone else’s: system time is real time. Re-run the Day 12 script afterwards to restore the fixed history (the cleanup section reminds you).

If you try UPDATE Lab.ProductListPrice SET ValidFrom = '2020-01-01' … you get an error: period columns are maintained by SQL Server.

E. Guided practical lab — Online delivery performance in working days

Operations promises to ship online orders within 2 working days of the order date (the order day itself not counted). Measure 2025 performance by month.

Step 1 — The orders to measure

1. Do: Select 2025 online orders that have a ship date.
2. Run:

   USE RetailLab;
   GO
   
   SELECT  OrderID, OrderDate, ShippedDate,
           DATEDIFF(DAY, OrderDate, ShippedDate) AS CalendarDays
   FROM    Sales.SalesOrder
   WHERE   SalesChannel = 'Online'
     AND   ShippedDate IS NOT NULL
     AND   OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'
   ORDER BY OrderDate, OrderID;

3. Observe: 106 rows (110 non-cancelled online orders minus 4 still pending).
4. Why it matters: Cancelled and pending orders have no ship date and are excluded by ShippedDate IS NOT NULL.
5. Confirm: 106 rows.

Step 2 — Count working days with the calendar

1. Do: For each order, count calendar days after the order date up to and including the ship date that are working days.
2. Run:

   SELECT  o.OrderID, o.OrderDate, o.ShippedDate,
           DATEDIFF(DAY, o.OrderDate, o.ShippedDate) AS CalendarDays,
           wd.WorkingDays
   FROM    Sales.SalesOrder AS o
   CROSS APPLY
   (
       SELECT COUNT(*) AS WorkingDays
       FROM   Lab.Calendar AS c
       WHERE  c.CalendarDate >  o.OrderDate
         AND  c.CalendarDate <= o.ShippedDate
         AND  c.IsWorkingDay = 1
   ) AS wd
   WHERE   o.SalesChannel = 'Online'
     AND   o.ShippedDate IS NOT NULL
     AND   o.OrderDate >= '2025-12-01' AND o.OrderDate < '2026-01-01'
   ORDER BY o.OrderDate, o.OrderID;

3. Observe: December rows such as order 1350 (ordered Wed 3 Dec, shipped Sun 7 Dec: 4 calendar days, 2 working days) and order 1360 (ordered Fri 19 Dec, shipped Sun 21 Dec: 2 calendar days, 0 working days).
4. Why it matters: Calendar days and working days tell different stories; the calendar table encodes the rule once.
5. Confirm: order 1350 → 4 and 2.

Step 3 — Flag the SLA and summarise by month

1. Do: Remove the December filter, wrap the step-2 query in a CTE and summarise.

2. Run:

   WITH Shipping AS
   (
       SELECT  o.OrderID, o.OrderDate,
               DATEDIFF(DAY, o.OrderDate, o.ShippedDate) AS CalendarDays,
               wd.WorkingDays
       FROM    Sales.SalesOrder AS o
       CROSS APPLY (SELECT COUNT(*) AS WorkingDays
                    FROM   Lab.Calendar AS c
                    WHERE  c.CalendarDate >  o.OrderDate
                      AND  c.CalendarDate <= o.ShippedDate
                      AND  c.IsWorkingDay = 1) AS wd
       WHERE   o.SalesChannel = 'Online'
         AND   o.ShippedDate IS NOT NULL
         AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
   )
   SELECT  MONTH(OrderDate)                                                    AS MonthNo,
           COUNT(*)                                                            AS ShippedOrders,
           SUM(CASE WHEN WorkingDays <= 2 THEN 1 ELSE 0 END)                   AS WithinSLA,
           CAST(100.0 * SUM(CASE WHEN WorkingDays <= 2 THEN 1 ELSE 0 END) / COUNT(*) AS decimal(5,1)) AS SLAPct,
           CAST(AVG(CAST(WorkingDays AS decimal(5,2))) AS decimal(4,1))         AS AvgWorkingDays
   FROM    Shipping
   GROUP BY MONTH(OrderDate)
   ORDER BY MonthNo;

3. Observe:

   MonthNo	ShippedOrders	WithinSLA	SLAPct	AvgWorkingDays
   1	5	5	100.0	1.4
   2	5	4	80.0	1.8
   3	12	8	66.7	2.3
   4	8	6	75.0	2.0
   5	7	6	85.7	1.4
   6	11	9	81.8	1.5
   7	13	11	84.6	1.5
   8	7	6	85.7	1.6
   9	9	9	100.0	1.0
   10	8	4	50.0	2.1
   11	7	6	85.7	1.7
   12	14	14	100.0	0.9

4. Why it matters: October stands out at 50%. December’s average is under one working day partly because of weekend and bank-holiday shipping — a business question worth raising.

5. Confirm: 12 rows; total shipped orders 106.

Step 4 — The annual figure

1. Do: Replace the final SELECT with a single-row summary.
2. Run:

   SELECT  COUNT(*)                                                             AS ShippedOrders,
           SUM(CASE WHEN WorkingDays  <= 2 THEN 1 ELSE 0 END)                   AS WithinSLA_WorkingDays,
           CAST(100.0 * SUM(CASE WHEN WorkingDays <= 2 THEN 1 ELSE 0 END) / COUNT(*) AS decimal(5,1)) AS SLAPct,
           SUM(CASE WHEN CalendarDays <= 2 THEN 1 ELSE 0 END)                   AS WithinTwoCalendarDays
   FROM    Shipping;

3. Observe: 106, 88, 83.0, 65.
4. Why it matters: Measured in calendar days, only 65 orders (61%) would meet a “2-day” target; in working days, 88 (83%). The definition changes the headline — agree it before reporting.
5. Confirm: 83.0%.

F. Independent practice

Task 1 — Tenure done properly

Using @AsAt date = '2025-06-30', return each employee hired on or before that date with EmployeeID, FullName, HireDate, TenureYears, TenureMonths (completed years and remaining completed months, up to the termination date for leavers) and NaiveYears (DATEDIFF(YEAR, HireDate, end date)). Sort by EmployeeID.

Success criteria: 15 rows (Oliver Grant joined later). Raj Patel: 5 years 9 months, naive 6. Five employees have NaiveYears one higher than TenureYears: 2, 6, 8, 11 and 13.

Task 2 — Price list changes since 2023

Using the temporal table, list products present on 1 January 2023 whose list price is different today, with ProductID, PriceOn20230101, CurrentPrice and ChangePct (1 decimal place).

Success criteria: 6 rows — products 1 (169.00 → 189.00), 2 (329.00 → 349.00), 5 (279.00 → 299.00), 9 (5.99 → 6.50), 10 and 11 (175.00 → 189.00).

Task 3 — Products added and removed since mid-2024

Compare the price list as of 2024-06-30 with the current one. Return ProductID, ProductCode and Change (Added or Removed).

Success criteria: 4 rows — 14 JKT-005, 28 BPK-003 and 29 NAV-004 Added; 30 TNT-004 Removed.

Task 4 — Versions that came and went (challenging)

a) Return every price version that began and ended within 2023 (CONTAINED IN), with ProductID, ListPrice, ValidFrom, ValidTo.

b) For product 22 (Handheld GPS Unit), use LAG over FOR SYSTEM_TIME ALL to show each version with the previous price and the change.

Success criteria: (a) 3 rows: product 1 at 179.00 (2023-03-01 → 2024-01-01), product 9 at 5.99 (2023-01-01 → 2024-01-01) and product 22 at 319.00 (2023-06-01 → 2023-11-15). (b) 2 rows: 319.00 with no previous price; 299.00 with previous 319.00 and change −20.00 (unless you changed product 22 yourself today).

Stretch challenge — Sales per working day, year on year

For each month, compare 2025 with 2024 per working day: MonthNo, WorkingDays2024, NetPerWD2024, WorkingDays2025, NetPerWD2025, ChangePct. Use Lab.Calendar for working days.

Success criteria: 12 rows. January: 22 / 158.06 vs 22 / 170.44 (+7.8%). March: 20 / 200.77 vs 21 / 327.97 (+63.4%). August: 21 / 279.67 vs 20 / 120.97 (−56.7%). December: 20 / 467.19 vs 21 / 462.79 (−0.9%).

G. Solutions and explanations

DECLARE @AsAt date = '2025-06-30';

SELECT  e.EmployeeID,
        CONCAT_WS(N' ', e.FirstName, e.LastName) AS FullName,
        e.HireDate,
        m.CompletedMonths / 12                    AS TenureYears,
        m.CompletedMonths % 12                    AS TenureMonths,
        DATEDIFF(YEAR, e.HireDate, x.EndDate)     AS NaiveYears
FROM    HR.Employee AS e
CROSS APPLY (VALUES (CASE WHEN e.TerminationDate < @AsAt THEN e.TerminationDate ELSE @AsAt END)) AS x (EndDate)
CROSS APPLY (VALUES (DATEDIFF(MONTH, e.HireDate, x.EndDate)
                     - CASE WHEN DAY(x.EndDate) < DAY(e.HireDate) THEN 1 ELSE 0 END)) AS m (CompletedMonths)
WHERE   e.HireDate <= @AsAt
ORDER BY e.EmployeeID;

SELECT  old.ProductID,
        old.ListPrice                                                         AS PriceOn20230101,
        cur.ListPrice                                                         AS CurrentPrice,
        CAST(100.0 * (cur.ListPrice - old.ListPrice) / old.ListPrice AS decimal(5,1)) AS ChangePct
FROM    Lab.ProductListPrice FOR SYSTEM_TIME AS OF '2023-01-01' AS old
JOIN    Lab.ProductListPrice AS cur ON cur.ProductID = old.ProductID
WHERE   cur.ListPrice <> old.ListPrice
ORDER BY old.ProductID;

WITH Then2024 AS (SELECT ProductID FROM Lab.ProductListPrice FOR SYSTEM_TIME AS OF '2024-06-30'),
     NowList  AS (SELECT ProductID FROM Lab.ProductListPrice),
     Changes  AS
     (
         SELECT ProductID, 'Added'   AS Change FROM (SELECT ProductID FROM NowList  EXCEPT SELECT ProductID FROM Then2024) AS a
         UNION ALL
         SELECT ProductID, 'Removed'           FROM (SELECT ProductID FROM Then2024 EXCEPT SELECT ProductID FROM NowList)  AS r
     )
SELECT  c.ProductID, p.ProductCode, c.Change
FROM    Changes AS c
JOIN    Inventory.Product AS p ON p.ProductID = c.ProductID
ORDER BY c.Change, c.ProductID;

-- (a)
SELECT  ProductID, ListPrice, ValidFrom, ValidTo
FROM    Lab.ProductListPrice FOR SYSTEM_TIME CONTAINED IN ('2023-01-01', '2024-01-01')
ORDER BY ProductID, ValidFrom;

-- (b)
SELECT  ProductID, ListPrice, ValidFrom, ValidTo,
        LAG(ListPrice) OVER (ORDER BY ValidFrom)             AS PreviousPrice,
        ListPrice - LAG(ListPrice) OVER (ORDER BY ValidFrom) AS PriceChange
FROM    Lab.ProductListPrice FOR SYSTEM_TIME ALL
WHERE   ProductID = 22
ORDER BY ValidFrom;

WITH WorkDays AS
(
    SELECT CalendarYear, MonthNo, SUM(CAST(IsWorkingDay AS int)) AS WorkingDays
    FROM   Lab.Calendar
    WHERE  CalendarYear IN (2024, 2025)
    GROUP BY CalendarYear, MonthNo
),
Sales AS
(
    SELECT  YEAR(o.OrderDate) AS Yr, MONTH(o.OrderDate) AS MonthNo,
            SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS NetSales
    FROM    Sales.SalesOrder AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.OrderStatus <> 'Cancelled'
    GROUP BY YEAR(o.OrderDate), MONTH(o.OrderDate)
),
PerWD AS
(
    SELECT w.CalendarYear, w.MonthNo, w.WorkingDays, s.NetSales / w.WorkingDays AS NetPerWD
    FROM   WorkDays AS w
    JOIN   Sales    AS s ON s.Yr = w.CalendarYear AND s.MonthNo = w.MonthNo
)
SELECT  a.MonthNo,
        a.WorkingDays                          AS WorkingDays2024,
        CAST(a.NetPerWD AS decimal(10,2))      AS NetPerWD2024,
        b.WorkingDays                          AS WorkingDays2025,
        CAST(b.NetPerWD AS decimal(10,2))      AS NetPerWD2025,
        CAST(100.0 * (b.NetPerWD - a.NetPerWD) / a.NetPerWD AS decimal(6,1)) AS ChangePct
FROM    PerWD AS a
JOIN    PerWD AS b ON b.MonthNo = a.MonthNo AND b.CalendarYear = 2025
WHERE   a.CalendarYear = 2024
ORDER BY a.MonthNo;

H. Knowledge check

1. Understanding. What does DATEDIFF(MONTH, '2025-03-31', '2025-04-01') return, and why?

2. Debugging. A report filters WHERE CreatedAt BETWEEN '2025-12-01' AND '2025-12-31' on a datetime2 column and misses some rows. Which ones, and what is the fix?

3. Understanding. Why might DATEPART(WEEKDAY, '2025-12-29') return different values for two colleagues?

4. Query interpretation. What does SELECT * FROM Lab.ProductListPrice FOR SYSTEM_TIME AS OF '2025-06-30' WHERE ProductID = 30; return?

5. Understanding. A price change approved on 15 January to take effect on 1 February is entered into a temporal table on 15 January. What does AS OF '2025-01-20' show, and why might that not be what Finance expects?

I. Troubleshooting and cleanup

Cleanup. Restore the fixed price history by re-running the Day 12 script (it drops and re-creates the three Lab objects):

-- Open and execute: scripts/lessons/day12_calendar_and_temporal.sql
-- Expected last grid: 1096, 760, 24, 29, 10, SYSTEM_VERSIONED_TEMPORAL_TABLE

Keep Lab.Calendar, Lab.ProductListPrice and Lab.ProductListPriceHistory: Days 14, 29 and 30 use them. Core data is unchanged.

J. Completion checklist, recap and references

Completion checklist

• The Day 12 script ran and showed 1096, 760, 24, 29, 10, SYSTEM_VERSIONED_TEMPORAL_TABLE.
• Worked example 3 returned 2 with BETWEEN and 3 with the half-open range.
• Worked example 6 wrote a history row with today’s UTC time.
• The lab returned 106 shipped orders, 88 within SLA (83.0%).
• Tasks 1–4 match the success criteria; you re-ran the Day 12 script afterwards.

Recap

• Use date for dates and datetime2(n) for timestamps; write 'YYYY-MM-DD' literals.
• DATEDIFF counts boundaries; compute completed months/years explicitly. DATEADD with months clamps to month end.
• Weekday numbers and names depend on session settings; calendars and fixed arithmetic don’t.
• Filter periods with half-open ranges; avoid functions on filtered columns.
• Calendar tables fill gaps and hold business-day rules.
• System-versioned temporal tables keep every previous row version automatically, stamped with UTC system time; query them with FOR SYSTEM_TIME AS OF / FROM … TO / BETWEEN / CONTAINED IN / ALL.
• System time is not business time.

Next: Day 13 — String functions and pattern matching: LIKE, PATINDEX, STRING_SPLIT, STRING_AGG, CONCAT and friends.

References (Microsoft Learn)

• Date and time data types and functions (Transact-SQL)
• DATEDIFF (Transact-SQL)
• DATEADD (Transact-SQL)
• DATETRUNC (Transact-SQL)
• SET DATEFIRST (Transact-SQL)
• Temporal tables
• Create a system-versioned temporal table — including migrating an existing solution
• Query data in a system-versioned temporal table
• UK bank holidays (GOV.UK) — source of the calendar’s holiday dates