SQL BI & Reporting

When to Use

• Writing summary reports with subtotals across multiple dimensions
• Building time-series queries (bucketing, gap filling, moving averages)
• Pivoting row data into columns or unpivoting columns into rows
• Generating date ranges or number sequences (tally tables)
• Solving gaps-and-islands problems (consecutive ranges, session analysis)
• Exporting query results as JSON, XML, or CSV
• Writing queries optimized for Power BI DirectQuery or SSRS
• Year-over-year, cohort, or funnel analysis

When NOT to use: application schema design (table design, naming conventions, access control), query performance tuning (execution plans, index tuning, wait stats), or ETL pipeline design.

Multi-Level Aggregation

Three operators produce subtotals from a single GROUP BY — choose based on your needs:

-- Hierarchical subtotals: year > quarter > grand total SELECT YEAR(OrderDate) AS OrderYear, DATEPART(quarter, OrderDate) AS OrderQtr, SUM(TotalAmount) AS Revenue FROM Sales.Orders GROUP BY ROLLUP( YEAR(OrderDate), DATEPART(quarter, OrderDate) ) ORDER BY OrderYear, OrderQtr;

Use GROUPING(col) to distinguish subtotal NULLs from real NULLs — returns 1 for subtotal rows, 0 for data rows.

Full reference: [Aggregation Patterns](references/aggregation-patterns.md) — GROUPING_ID, conditional aggregation, HAVING vs WHERE, COUNT semantics with NULLs.

Window Functions

Quick Reference

Running total

SELECT OrderDate, TotalAmount, SUM(TotalAmount) OVER ( ORDER BY OrderDate ROWS UNBOUNDED PRECEDING ) AS RunningTotal FROM Sales.Orders;

Always specify ROWS explicitly. The default frame (when ORDER BY is present but no frame is specified) is RANGE UNBOUNDED PRECEDING — this includes ties, produces different results, and is slower.

Moving average

-- 7-day moving average AVG(DailyRevenue) OVER ( ORDER BY OrderDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS MovingAvg7Day

Period-over-period comparison

-- Year-over-year monthly revenue (offset 12 for monthly data) LAG(Revenue, 12) OVER (ORDER BY OrderYear, OrderMonth) AS PriorYearRevenue

Greatest-N-per-group

-- Most recent order per customer WITH Ranked AS ( SELECT , ROW_NUMBER() OVER ( PARTITION BY CustomerID ORDER BY OrderDate DESC ) AS RN FROM Sales.Orders ) SELECT FROM Ranked WHERE RN = 1;

Named WINDOW clause (2022+)

When multiple window functions share the same partition and order, avoid repetition:

SELECT OrderID, ROW_NUMBER() OVER Win AS RowNum, SUM(TotalAmount) OVER Win AS RunningTotal, LAG(TotalAmount) OVER Win AS PrevAmount FROM Sales.Orders WINDOW Win AS (PARTITION BY CustomerID ORDER BY OrderDate);

Date Math Patterns

Bucketing

SARGable date filters

-- GOOD: index seek WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'

-- BAD: index scan (function on column) WHERE YEAR(OrderDate) = 2024

Gap filling

LEFT JOIN from a continuous date spine (tally table or calendar table) to your sparse data. ISNULL replaces NULL with zero for missing dates. Use a range join to keep the date column SARGable — never CAST(col AS DATE) in a JOIN:

SELECT C.FullDate, ISNULL(SUM(O.TotalAmount), 0) AS Revenue FROM dbo.Calendar C LEFT JOIN Sales.Orders O ON O.OrderDate >= C.FullDate AND O.OrderDate < DATEADD(day, 1, C.FullDate) WHERE C.FullDate >= '2024-01-01' AND C.FullDate < '2025-01-01' GROUP BY C.FullDate ORDER BY C.FullDate;

Full reference: [Time Series](references/time-series.md) — calendar table design, fiscal calendars, cohort analysis, temporal table AS OF queries, moving averages.

Tally Tables

Generate number sequences with zero I/O using the stacking CTE pattern:

WITH L0 AS (SELECT 1 AS c UNION ALL SELECT 1), L1 AS (SELECT 1 AS c FROM L0 CROSS JOIN L0 AS B), L2 AS (SELECT 1 AS c FROM L1 CROSS JOIN L1 AS B), L3 AS (SELECT 1 AS c FROM L2 CROSS JOIN L2 AS B), L4 AS (SELECT 1 AS c FROM L3 CROSS JOIN L3 AS B), Nums AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM L4 ) SELECT N FROM Nums WHERE N <= @Count; -- REQUIRED: limits output to needed rows

Every tally CTE must include WHERE N <= <limit> — without it, the full 65,536 rows are generated. When used for date spines, the limit is DATEDIFF(day, @Start, @End). Never omit the limit and rely on an outer query to filter — the optimizer may still materialize all rows.

On SQL Server 2022+, use GENERATE_SERIES(1, @Count) for simpler syntax. Do not use recursive CTEs for number generation — they are row-by-row and 10-50x slower.

Full reference: [Tally Tables](references/tally-tables.md) — inline function wrapper, date range generation, gap filling, string splitting, permanent vs inline tradeoffs.

Pivot & Unpivot

Static PIVOT (known columns):

SELECT ProductName, [Q1], [Q2], [Q3], [Q4] FROM (...) AS Src PIVOT (SUM(Revenue) FOR Qtr IN ([Q1],[Q2],[Q3],[Q4])) AS Pvt;

Dynamic PIVOT (runtime columns): build with STRINGAGG + QUOTENAME + spexecutesql. QUOTENAME prevents SQL injection.

Unpivot: use CROSS APPLY VALUES instead of the UNPIVOT operator. UNPIVOT silently drops NULL rows; CROSS APPLY VALUES preserves them.

CROSS APPLY ( VALUES ('Q1', S.Q1), ('Q2', S.Q2), ('Q3', S.Q3), ('Q4', S.Q4) ) AS Q(Quarter, Revenue)

Full reference: [Pivot & Unpivot](references/pivot-unpivot.md) — dynamic PIVOT pattern, multi-column unpivot, conditional aggregation alternative.

Gaps and Islands

Detecting consecutive sequences (islands) and breaks (gaps) in ordered data.

Island detection — the ROW_NUMBER difference technique:

-- GroupKey is constant within each consecutive run DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY SensorID ORDER BY ReadingDate), ReadingDate ) AS GroupKey

Group by GroupKey to find each island's start, end, and length.

Gap detection — LEAD to find the next value, then check for breaks:

LEAD(OrderDay) OVER (ORDER BY OrderDay) AS NextDay -- Gap exists when NextDay - OrderDay > 1

Full reference: [Gaps and Islands](references/gaps-and-islands.md) — session analysis, status change tracking, date-based vs sequence-based patterns.

Data Export

FOR JSON PATH nesting

Use dot-notation aliases to control JSON structure — no subqueries needed for flat nesting:

SELECT O.OrderID AS "id", C.CustomerName AS "customer.name", C.Email AS "customer.email" FROM Sales.Orders O JOIN Sales.Customers C ON C.CustomerID = O.CustomerID FOR JSON PATH; -- {"id":1001,"customer":{"name":"Acme","email":"[email protected]"}}

Full reference: [Export Patterns](references/export-patterns.md) — FOR JSON nesting, JSON_OBJECT (2022+), Power BI DirectQuery optimization, BCP parameters.

Validation Checklist

Before finalizing any BI query, verify:

1. Subtotal rows — GROUPING(col) = 1 filters identify rollup rows; confirm NULL isn't confused with real data
2. Window frames — every windowed aggregate has an explicit ROWS BETWEEN clause
3. Date filters — all WHERE and JOIN predicates on date columns use range predicates, never CAST(col AS DATE) or YEAR(col)
4. Gap fills — LEFT JOIN from calendar/tally produces rows for every expected period; ISNULL handles missing data
5. Dynamic PIVOT — generated SQL is printed and inspected before sp_executesql; all column names pass through QUOTENAME
6. Export shape — FOR JSON PATH output matches the consumer's expected schema; test with a LIMIT before full run

Common Mistakes

Reference Files