Sql Bi Reporting

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:

Copy code
-- 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 — GROUPING_ID, conditional aggregation, HAVING vs WHERE, COUNT semantics with NULLs.

Window Functions

Quick Reference

Running total

Copy code
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

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

Period-over-period comparison

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

Greatest-N-per-group

Copy code
-- 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:

Copy code
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

Copy code
-- 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:

Copy code
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 — 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:

Copy code
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 — inline function wrapper, date range generation, gap filling, string splitting, permanent vs inline tradeoffs.

Pivot & Unpivot

Static PIVOT (known columns):

Copy code
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 STRING_AGG + QUOTENAME + sp_executesql. QUOTENAME prevents SQL injection.

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

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

Full reference: Pivot & Unpivot — 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:

Copy code
-- 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:

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

Full reference: Gaps and Islands — 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:

Copy code
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":"info@acme.com"}}

Full reference: Export Patterns — 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