Day 14: Week 2 review & complex queries

A. Lesson overview

Activity breakdown

Business scenario. The commercial director wants a one-page “location performance pack” for 2025 — each shop and the online channel side by side, with quarterly sales, growth on 2024, customer reach, the best-selling product and who staffed the shop. Merchandising wants a readable price-history note per product, and Operations wants a weekly heat map of order activity. Each request uses several Week 2 techniques at once.

B. Theory explained clearly

Part 1 — Week 2 in one page

Part 2 — A method for complex queries

1. Write the output specification first. One row per what? Which columns, from which tables, at which grain?
2. Identify the grains involved. For the location pack: order lines → orders → (location, quarter) → location. Each change of grain is a CTE.
3. Build and validate one CTE at a time. Run each on its own; compare with a known control total (2025 net sales = 63,741.94).
4. Join only at matching grains. Aggregate child data to the parent grain before joining (Day 8). APPLY with an aggregate or TOP (1) keeps the outer grain.
5. Add presentation last. Rounding, labels, formatting and ordering belong in the final SELECT.
6. Reconcile. A final check (totals match, row counts as expected) is part of the query, not an afterthought.

Part 3 — Three integration traps

ISO weeks belong to an ISO year

ISO weeks run Monday–Sunday, and week 1 is the week containing the first Thursday of the year. So 29–31 December 2025 belong to ISO week 1 of 2026, and 1–4 January 2026 do too; meanwhile 30 December 2024–5 January 2025 is week 1 of 2025. Grouping 2025 dates by DATEPART(ISO_WEEK, d) alone merges late-December 2025 days into “week 1” with early-January 2025 days. Always group by (ISO year, ISO week). The ISO year is the calendar year of the week’s Thursday:

YEAR(DATEADD(DAY, 4 - IsoDayOfWeek, CalendarDate))     -- IsoDayOfWeek: 1 = Monday … 7 = Sunday

APPLY keeps the grain only if it returns one row

OUTER APPLY (SELECT TOP (1) …) and OUTER APPLY (SELECT COUNT(*), SUM(…) …) return exactly one row per outer row. An APPLY that can return several rows multiplies the outer row — fine when intended (top 3 per store), fan-out when not.

FORMAT is for presentation, and it is slow

FORMAT(1446.29, 'C', 'en-GB') gives £1,446.29; FORMAT(d, 'd MMM yyyy', 'en-GB') gives 29 Dec 2025. FORMAT uses .NET formatting and is much slower than CONVERT/CAST on large results, and it returns nvarchar — so you can no longer sort or sum the result numerically. Format only in the final SELECT, only for display, ideally in the reporting tool.

C. Dataset and starting state

Preparation.

1. If RetailLab may have been changed, run scripts/setup/04_reset_training_data.sql in SQLCMD Mode (destructive), then scripts/lessons/day12_calendar_and_temporal.sql (download it).
2. If you only changed prices on Day 12, re-running day12_calendar_and_temporal.sql is enough.

Validation. Expected: 1096, 29, 10, 63741.94.

USE RetailLab;
GO

SELECT  (SELECT COUNT(*) FROM Lab.Calendar)                AS CalendarDays,
        (SELECT COUNT(*) FROM Lab.ProductListPrice)        AS CurrentPrices,
        (SELECT COUNT(*) FROM Lab.ProductListPriceHistory) AS PriceHistoryRows,
        (SELECT CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(12,2))
         FROM   Sales.SalesOrder AS o JOIN Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
         WHERE  o.OrderStatus <> 'Cancelled'
           AND  o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01') AS NetSales2025;

If Lab.Calendar is missing (Invalid object name), run the Day 12 script.

D. Worked examples

Worked example 1 — The ISO week trap

-- Wrong: groups by ISO week number only
SELECT  c.IsoWeek, COUNT(o.OrderID) AS Orders, MIN(c.CalendarDate) AS FirstDay, MAX(c.CalendarDate) AS LastDay
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.IsoWeek
HAVING  c.IsoWeek = 1;

-- Right: group by ISO year and ISO week
SELECT  iy.IsoYear, c.IsoWeek, COUNT(o.OrderID) AS Orders, MIN(c.CalendarDate) AS FirstDay, MAX(c.CalendarDate) AS LastDay
FROM    Lab.Calendar AS c
CROSS APPLY (VALUES (YEAR(DATEADD(DAY, 4 - c.IsoDayOfWeek, c.CalendarDate)))) AS iy (IsoYear)
LEFT JOIN Sales.SalesOrder AS o ON o.OrderDate = c.CalendarDate AND o.OrderStatus <> 'Cancelled'
WHERE   c.CalendarYear = 2025
GROUP BY iy.IsoYear, c.IsoWeek
HAVING  c.IsoWeek = 1
ORDER BY iy.IsoYear;

Expected result: the wrong query returns one “week 1” from 2025-01-01 to 2025-12-31 with 6 orders. The right query returns two rows: ISO year 2025, week 1 (1–5 January) with 2 orders, and ISO year 2026, week 1 (29–31 December 2025 in this filter) with 4 orders.

Worked example 2 — Readable price history from the temporal table

WITH Versions AS
(
    SELECT  ProductID, ListPrice, ValidFrom
    FROM    Lab.ProductListPrice FOR SYSTEM_TIME ALL
)
SELECT  v.ProductID,
        p.ProductCode,
        COUNT(*)                                                                       AS Versions,
        STRING_AGG(CAST(v.ListPrice AS nvarchar(12)), N' → ') WITHIN GROUP (ORDER BY v.ValidFrom) AS PriceHistory
FROM    Versions AS v
JOIN    Inventory.Product AS p ON p.ProductID = v.ProductID
GROUP BY v.ProductID, p.ProductCode
HAVING  COUNT(*) > 1
ORDER BY v.ProductID;

Expected result: 7 rows — product 1 169.00 → 179.00 → 189.00; 2 329.00 → 349.00; 5 279.00 → 299.00; 9 5.50 → 5.99 → 6.50; 10 and 11 175.00 → 189.00; 22 319.00 → 299.00. The arrow is a Unicode character, so the query uses an N' → ' literal and nvarchar casts. With a varchar literal, → would be converted to the database code page (1252), which has no arrow, and would display as ?.

Worked example 3 — Formatting for a sentence

WITH DecSummary AS
(
    SELECT  o.CustomerID,
            COUNT(DISTINCT o.OrderID)                            AS Orders,
            SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))  AS NetSales,
            MAX(o.OrderDate)                                     AS LastOrder
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= '2025-12-01' AND o.OrderDate < '2026-01-01'
    GROUP BY o.CustomerID
)
SELECT TOP (3)
        CONCAT(c.FirstName, N' ', c.LastName, N': ', d.Orders,
               CASE WHEN d.Orders = 1 THEN N' order, ' ELSE N' orders, ' END,
               FORMAT(d.NetSales, 'C', 'en-GB'), N', last on ',
               FORMAT(d.LastOrder, 'd MMM yyyy', 'en-GB'))      AS Summary
FROM    DecSummary AS d
JOIN    Sales.Customer AS c ON c.CustomerID = d.CustomerID
ORDER BY d.NetSales DESC, d.CustomerID;                      -- sort on the number, not the text

Expected result:

1. Alice Morgan: 3 orders, £1,446.29, last on 29 Dec 2025
2. Xavier Lopez: 1 order, £1,153.30, last on 13 Dec 2025
3. Ben Carter: 2 orders, £904.00, last on 22 Dec 2025

E. Guided practical lab — 2025 location performance pack

Output specification: one row per location (three shops + Online) with Q1–Q4 2025 net sales, Net2025, Net2024, YoYPct, Customers2025, TopProduct2025 and Staff2025 (first names of salespeople who served there in 2025, alphabetical). Then a total row check.

Step 1 — Order grain: one row per non-cancelled order with its location

1. Do: Build the base CTE.
2. Run:

   USE RetailLab;
   GO
   
   WITH OrderBase AS
   (
       SELECT  o.OrderID, o.CustomerID, o.EmployeeID, o.OrderDate,
               COALESCE(s.StoreName, N'Online')                        AS Location,
               SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))    AS OrderNet
       FROM    Sales.SalesOrder     AS o
       JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
       LEFT JOIN Sales.Store        AS s ON s.StoreID = o.StoreID
       WHERE   o.OrderStatus <> 'Cancelled'
       GROUP BY o.OrderID, o.CustomerID, o.EmployeeID, o.OrderDate, s.StoreName
   )
   SELECT Location, COUNT(*) AS Orders, CAST(SUM(OrderNet) AS decimal(12,2)) AS NetAllTime
   FROM   OrderBase
   GROUP BY Location
   ORDER BY Location;

3. Observe: 4 rows; the NetAllTime values add up to 122354.88.
4. Why it matters: LEFT JOIN to Sales.Store keeps online orders (NULL StoreID), labelled with COALESCE — safe because StoreName is never NULL in a real match.
5. Confirm: 369 orders in total.

Step 2 — Quarterly pivot for 2025

1. Do: Add a Quarterly CTE that pivots 2025 order values by quarter.

2. Run: replace the final SELECT with:

   , Quarterly AS
   (
       SELECT  Location, [1] AS Q1, [2] AS Q2, [3] AS Q3, [4] AS Q4
       FROM   (SELECT Location, DATEPART(QUARTER, OrderDate) AS Qtr, OrderNet
               FROM   OrderBase
               WHERE  OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01') AS src
       PIVOT  (SUM(OrderNet) FOR Qtr IN ([1], [2], [3], [4])) AS p
   )
   SELECT Location,
          CAST(Q1 AS decimal(12,2)) AS Q1, CAST(Q2 AS decimal(12,2)) AS Q2,
          CAST(Q3 AS decimal(12,2)) AS Q3, CAST(Q4 AS decimal(12,2)) AS Q4
   FROM   Quarterly
   ORDER BY Location;

3. Observe:

   Location	Q1	Q2	Q3	Q4
   Edinburgh Old Town	2145.89	2642.30	382.29	4804.08
   London Covent Garden	2516.16	1048.27	2534.27	1025.65
   Manchester Northern Quarter	3035.90	5330.42	805.94	3080.83
   Online	6830.51	9036.81	8988.47	9534.14

4. Why it matters: The pivot source has exactly three columns (Day 11’s implicit-grouping rule).

5. Confirm: every quarter column adds up to the Day 2 quarterly totals (14528.46, 18057.81, 12710.98, 18444.70).

Step 3 — Year comparison and reach (conditional aggregation)

1. Do: Add a Yearly CTE at location grain.
2. Run: add after Quarterly:

   , Yearly AS
   (
       SELECT  Location,
               SUM(CASE WHEN OrderDate <  '2025-01-01' THEN OrderNet ELSE 0 END)                    AS Net2024,
               SUM(CASE WHEN OrderDate >= '2025-01-01' THEN OrderNet ELSE 0 END)                    AS Net2025,
               COUNT(DISTINCT CASE WHEN OrderDate >= '2025-01-01' THEN CustomerID END)              AS Customers2025
       FROM    OrderBase
       GROUP BY Location
   )

   and test it with SELECT * FROM Yearly ORDER BY Location; as the final statement.
3. Observe: Edinburgh 3363.97 → 9974.56 (17 customers); London 7785.37 → 7124.35 (15); Manchester 12776.43 → 12253.09 (22); Online 34687.16 → 34389.94 (30).
4. Why it matters: COUNT(DISTINCT CASE … THEN CustomerID END) counts 2025 customers only — the CASE returns NULL for 2024 orders, which COUNT ignores.
5. Confirm: Net2025 sums to 63741.94.

Step 4 — Top product and staff list with APPLY

1. Do: Write the final SELECT, joining the two location-grain CTEs and adding two OUTER APPLYs that each return one row per location.

2. Run:

   SELECT  q.Location,
           CAST(q.Q1 AS decimal(12,2)) AS Q1, CAST(q.Q2 AS decimal(12,2)) AS Q2,
           CAST(q.Q3 AS decimal(12,2)) AS Q3, CAST(q.Q4 AS decimal(12,2)) AS Q4,
           CAST(y.Net2025 AS decimal(12,2))                                         AS Net2025,
           CAST(y.Net2024 AS decimal(12,2))                                         AS Net2024,
           CAST(100.0 * (y.Net2025 - y.Net2024) / NULLIF(y.Net2024, 0) AS decimal(6,1)) AS YoYPct,
           y.Customers2025,
           tp.ProductName                                                           AS TopProduct2025,
           st.Staff                                                                 AS Staff2025
   FROM    Quarterly AS q
   JOIN    Yearly    AS y ON y.Location = q.Location
   OUTER APPLY
   (
       SELECT TOP (1) p.ProductName
       FROM   OrderBase            AS ob
       JOIN   Sales.SalesOrderLine AS l ON l.OrderID   = ob.OrderID
       JOIN   Inventory.Product    AS p ON p.ProductID = l.ProductID
       WHERE  ob.Location = q.Location
         AND  ob.OrderDate >= '2025-01-01' AND ob.OrderDate < '2026-01-01'
       GROUP BY p.ProductName, p.ProductCode
       ORDER BY SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) DESC, p.ProductCode
   ) AS tp
   OUTER APPLY
   (
       SELECT STRING_AGG(e.FirstName, N', ') WITHIN GROUP (ORDER BY e.FirstName) AS Staff
       FROM  (SELECT DISTINCT ob.EmployeeID
              FROM   OrderBase AS ob
              WHERE  ob.Location = q.Location
                AND  ob.EmployeeID IS NOT NULL
                AND  ob.OrderDate >= '2025-01-01' AND ob.OrderDate < '2026-01-01') AS d
       JOIN   HR.Employee AS e ON e.EmployeeID = d.EmployeeID
   ) AS st
   ORDER BY CASE WHEN q.Location = N'Online' THEN 1 ELSE 0 END, q.Location;

3. Observe:

   Location	Net2025	Net2024	YoYPct	Customers2025	TopProduct2025	Staff2025
   Edinburgh Old Town	9974.56	3363.97	196.5	17	Handheld GPS Unit	Chen, Daniel, James, Liam, Megan, Oliver, Sophie
   London Covent Garden	7124.35	7785.37	-8.5	15	Summit 4-Person Tent	Chen, Daniel, Liam, Megan, Oliver, Sophie
   Manchester Northern Quarter	12253.09	12776.43	-4.1	22	Summit 4-Person Tent	Chen, Daniel, James, Liam, Megan, Oliver, Sophie
   Online	34389.94	34687.16	-0.9	30	Summit 4-Person Tent	NULL

4. Why it matters: Edinburgh’s +196.5% reflects a full year of trading against four months in 2024 — the number is right, the comparison is not like-for-like. A good report says so. The staff list for Online is NULL: STRING_AGG over no rows returns NULL.

5. Confirm: 4 rows; Net2025 adds up to 63741.94.

Step 5 — Built-in reconciliation

1. Do: Add a final check that the pack reconciles to the source.
2. Run: replace the final SELECT with:

   SELECT  CAST(SUM(COALESCE(Q1,0) + COALESCE(Q2,0) + COALESCE(Q3,0) + COALESCE(Q4,0)) AS decimal(12,2)) AS PivotTotal,
           (SELECT CAST(SUM(Net2025) AS decimal(12,2)) FROM Yearly)                                     AS YearlyTotal,
           CASE WHEN SUM(COALESCE(Q1,0) + COALESCE(Q2,0) + COALESCE(Q3,0) + COALESCE(Q4,0))
                   = (SELECT SUM(Net2025) FROM Yearly) THEN 'Reconciled' ELSE 'MISMATCH' END             AS CheckResult
   FROM    Quarterly;

3. Observe: 63741.94, 63741.94, Reconciled.
4. Why it matters: Two independently built CTEs agree — evidence that neither the pivot nor the conditional aggregation lost or duplicated anything.
5. Confirm: Reconciled.

F. Independent practice

Task 1 — Quiet weeks in 2025

Using Lab.Calendar, return every (ISO year, ISO week) that contains 2025 dates and had no non-cancelled orders, with the first and last 2025 date of that week.

Success criteria: 1 row — ISO year 2025, week 33, 2025-08-11 to 2025-08-17 (the warehouse migration). There are 53 (ISO year, week) groups in total for 2025’s dates; grouping by week number alone gives 52.

Task 2 — Price history notes

Return one row for every product that appears in the temporal table (current or historical), with ProductCode, Versions, PriceHistory (prices in order, separated by N' → ') and Status (Current if the product has a current row, otherwise Removed).

Success criteria: 30 rows. Product 9: 3 versions, 5.50 → 5.99 → 6.50, Current. Product 30 (TNT-004): 1 version, 599.00, Removed.

Task 3 — Sold everywhere in 2025

Using INTERSECT, find products sold (non-cancelled) at all four locations in 2025. Return the product codes.

Success criteria: 15 products: BPK-001, BPK-002, BSL-001, BSL-003, COK-001, COK-002, COK-003, JKT-001, JKT-002, NAV-001, NAV-002, NAV-003, TNT-001, TNT-002, TNT-003.

Task 4 — December sentence summaries

Produce Worked example 3’s sentence for all 20 customers who ordered in December 2025, sorted by net sales descending. Add a second column, NetSales (numeric), so the result can still be sorted and totalled.

Success criteria: 20 rows; first Alice Morgan: 3 orders, £1,446.29, last on 29 Dec 2025; NetSales adds up to 9718.65.

Stretch challenge — Q4 2025 weekly heat map (dynamic or static pivot)

Show orders per day for Q4 2025 as a grid: one row per (ISO year, ISO week), columns Mon…Sun, with 0 for days with no orders (including days outside the quarter in the first and last weeks), plus a Total column.

Success criteria: 14 rows from 2025-W40 to 2026-W01. 2025-W52 (22–28 December) is the busiest week: 2, 2, 1, 2, 1, 1, 1 = 10. The Total column adds up to 56.

G. Solutions and explanations

WITH DaysWithOrders AS
(
    SELECT  c.CalendarDate, c.IsoWeek,
            YEAR(DATEADD(DAY, 4 - c.IsoDayOfWeek, c.CalendarDate)) AS IsoYear,
            COUNT(o.OrderID)                                       AS Orders
    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.CalendarDate, c.IsoWeek, c.IsoDayOfWeek
)
SELECT  IsoYear, IsoWeek, MIN(CalendarDate) AS FirstDate, MAX(CalendarDate) AS LastDate
FROM    DaysWithOrders
GROUP BY IsoYear, IsoWeek
HAVING  SUM(Orders) = 0
ORDER BY IsoYear, IsoWeek;

WITH Versions AS
(
    SELECT ProductID, ListPrice, ValidFrom
    FROM   Lab.ProductListPrice FOR SYSTEM_TIME ALL
)
SELECT  p.ProductCode,
        COUNT(*)                                                                               AS Versions,
        STRING_AGG(CAST(v.ListPrice AS nvarchar(12)), N' → ') WITHIN GROUP (ORDER BY v.ValidFrom) AS PriceHistory,
        CASE WHEN EXISTS (SELECT 1 FROM Lab.ProductListPrice AS cur WHERE cur.ProductID = v.ProductID)
             THEN 'Current' ELSE 'Removed' END                                                 AS Status
FROM    Versions AS v
JOIN    Inventory.Product AS p ON p.ProductID = v.ProductID
GROUP BY v.ProductID, p.ProductCode
ORDER BY p.ProductCode;

WITH Sold AS
(
    SELECT DISTINCT COALESCE(s.StoreName, N'Online') AS Location, p.ProductCode
    FROM   Sales.SalesOrder     AS o
    JOIN   Sales.SalesOrderLine AS l ON l.OrderID   = o.OrderID
    JOIN   Inventory.Product    AS p ON p.ProductID = l.ProductID
    LEFT JOIN Sales.Store       AS s ON s.StoreID   = o.StoreID
    WHERE  o.OrderStatus <> 'Cancelled'
      AND  o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
)
SELECT ProductCode FROM Sold WHERE Location = N'Online'
INTERSECT
SELECT ProductCode FROM Sold WHERE Location = N'London Covent Garden'
INTERSECT
SELECT ProductCode FROM Sold WHERE Location = N'Manchester Northern Quarter'
INTERSECT
SELECT ProductCode FROM Sold WHERE Location = N'Edinburgh Old Town'
ORDER BY ProductCode;

-- Scalable alternative
WITH Sold AS ( /* same as above */
    SELECT DISTINCT COALESCE(s.StoreName, N'Online') AS Location, p.ProductCode
    FROM   Sales.SalesOrder AS o
    JOIN   Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    JOIN   Inventory.Product    AS p ON p.ProductID = l.ProductID
    LEFT JOIN Sales.Store       AS s ON s.StoreID = o.StoreID
    WHERE  o.OrderStatus <> 'Cancelled' AND o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
)
SELECT   ProductCode
FROM     Sold
GROUP BY ProductCode
HAVING   COUNT(DISTINCT Location) = (SELECT COUNT(DISTINCT Location) FROM Sold)
ORDER BY ProductCode;

WITH DecSummary AS
(
    SELECT  o.CustomerID,
            COUNT(DISTINCT o.OrderID)                            AS Orders,
            SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))  AS NetSales,
            MAX(o.OrderDate)                                     AS LastOrder
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= '2025-12-01' AND o.OrderDate < '2026-01-01'
    GROUP BY o.CustomerID
)
SELECT  CONCAT(c.FirstName, N' ', c.LastName, N': ', d.Orders,
               CASE WHEN d.Orders = 1 THEN N' order, ' ELSE N' orders, ' END,
               FORMAT(d.NetSales, 'C', 'en-GB'), N', last on ',
               FORMAT(d.LastOrder, 'd MMM yyyy', 'en-GB'))   AS Summary,
        CAST(d.NetSales AS decimal(12,2))                    AS NetSales
FROM    DecSummary AS d
JOIN    Sales.Customer AS c ON c.CustomerID = d.CustomerID
ORDER BY d.NetSales DESC, d.CustomerID;

WITH Q4Days AS
(
    SELECT  c.CalendarDate,
            YEAR(DATEADD(DAY, 4 - c.IsoDayOfWeek, c.CalendarDate)) AS IsoYear,
            c.IsoWeek,
            LEFT(c.DayName, 3)                                      AS Dy,
            COUNT(o.OrderID)                                        AS Orders
    FROM    Lab.Calendar AS c
    LEFT JOIN Sales.SalesOrder AS o
           ON o.OrderDate = c.CalendarDate AND o.OrderStatus <> 'Cancelled'
    WHERE   c.CalendarDate >= '2025-10-01' AND c.CalendarDate < '2026-01-01'
    GROUP BY c.CalendarDate, c.IsoDayOfWeek, c.IsoWeek, c.DayName
)
SELECT  CONCAT(IsoYear, '-W', RIGHT(CONCAT('0', IsoWeek), 2)) AS IsoYearWeek,
        COALESCE([Mon], 0) AS Mon, COALESCE([Tue], 0) AS Tue, COALESCE([Wed], 0) AS Wed,
        COALESCE([Thu], 0) AS Thu, COALESCE([Fri], 0) AS Fri, COALESCE([Sat], 0) AS Sat,
        COALESCE([Sun], 0) AS Sun,
        COALESCE([Mon],0) + COALESCE([Tue],0) + COALESCE([Wed],0) + COALESCE([Thu],0)
      + COALESCE([Fri],0) + COALESCE([Sat],0) + COALESCE([Sun],0) AS Total
FROM   (SELECT IsoYear, IsoWeek, Dy, Orders FROM Q4Days) AS src
PIVOT  (SUM(Orders) FOR Dy IN ([Mon], [Tue], [Wed], [Thu], [Fri], [Sat], [Sun])) AS p
ORDER BY IsoYear, IsoWeek;

H. Knowledge check (Week 2)

1. Understanding. A report joins Sales.SalesOrder to both Sales.SalesOrderLine and Sales.Payment and sums payments. What goes wrong, and what is the standard fix?

2. Query interpretation. SELECT Email FROM Sales.Customer INTERSECT SELECT TRIM(Email) FROM Staging.CustomerImport; returns one row more than you expected. Which row, and why?

3. Debugging. A PIVOT of monthly sales by channel returns hundreds of rows. What do you check first?

4. Understanding. When would you choose OUTER APPLY (SELECT TOP (1) …) over ROW_NUMBER() in a CTE?

5. Mixed. Name the technique for each: (a) all product–warehouse combinations including ones with no stock row; (b) the price list as it stood on 1 March 2023; (c) the second word of a name; (d) one row per tag from a comma-separated column; (e) days between order and shipment excluding weekends and bank holidays.

I. Troubleshooting and cleanup

Cleanup. Day 14 is read-only; nothing to clean up. Keep the Day 12 objects. Week 3 changes data, so each lesson provides its own safe starting state.

J. Completion checklist, recap and references

Completion checklist

• Worked example 1 showed one merged “week 1” (6 orders) and two correct week-1 rows (2 and 4).
• Your location pack shows four rows and reconciles (63741.94, Reconciled).
• Tasks 1–4 match the success criteria (1, 30, 15 and 20 rows).
• You can explain why Edinburgh’s +196.5% is not a like-for-like comparison.
• You answered the Week 2 knowledge check without looking back.

Week 2 recap

Next: Week 3 begins with Day 15 — Advanced INSERT, UPDATE and DELETE: the OUTPUT clause, MERGE (and its caveats), table variables and safe modification habits.

References (Microsoft Learn)

• WITH common_table_expression (Transact-SQL)
• Using PIVOT and UNPIVOT
• DATEPART (Transact-SQL) — ISO_WEEK
• FORMAT (Transact-SQL)
• Query data in a system-versioned temporal table
• STRING_AGG (Transact-SQL)