Specialized Patterns

Advanced query patterns for dates, hierarchies, pivoting data, and complex aggregations.

---

Advanced Date Handling

Date Functions Reference

Function	Description	Example
SYSDATE	Current date/time	SYSDATE
TRUNC(date, 'MM')	First of month	TRUNC(SYSDATE, 'MM')
TRUNC(date, 'YYYY')	First of year	TRUNC(SYSDATE, 'YYYY')
ADD_MONTHS(date, n)	Add months	ADD_MONTHS(SYSDATE, -3)
LAST_DAY(date)	Last day of month	LAST_DAY(SYSDATE)
EXTRACT(part FROM date)	Get year/month/day	EXTRACT(YEAR FROM trandate)

Current Period Queries

-- Today's transactions
SELECT * FROM transaction
WHERE TRUNC(trandate) = TRUNC(SYSDATE)

-- This week (Sunday start)
SELECT * FROM transaction
WHERE trandate >= TRUNC(SYSDATE, 'IW')
  AND trandate < TRUNC(SYSDATE, 'IW') + 7

-- This month
SELECT * FROM transaction
WHERE trandate >= TRUNC(SYSDATE, 'MM')
  AND trandate < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1)

-- This quarter
SELECT * FROM transaction
WHERE trandate >= TRUNC(SYSDATE, 'Q')
  AND trandate < ADD_MONTHS(TRUNC(SYSDATE, 'Q'), 3)

-- This year (YTD)
SELECT * FROM transaction
WHERE trandate >= TRUNC(SYSDATE, 'YYYY')

Previous Period Queries

-- Last month
SELECT * FROM transaction
WHERE trandate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
  AND trandate < TRUNC(SYSDATE, 'MM')

-- Same month last year
SELECT * FROM transaction
WHERE EXTRACT(MONTH FROM trandate) = EXTRACT(MONTH FROM SYSDATE)
  AND EXTRACT(YEAR FROM trandate) = EXTRACT(YEAR FROM SYSDATE) - 1

-- Last 30 days
SELECT * FROM transaction
WHERE trandate >= SYSDATE - 30

-- Last 12 months (rolling)
SELECT * FROM transaction
WHERE trandate >= ADD_MONTHS(SYSDATE, -12)

Test Case: Monthly Trend Report

-- Sales by month with YoY comparison
SELECT
    TO_CHAR(trandate, 'YYYY-MM') AS month,
    EXTRACT(YEAR FROM trandate) AS year,
    EXTRACT(MONTH FROM trandate) AS month_num,
    TO_CHAR(trandate, 'Month') AS month_name,
    COUNT(*) AS order_count,
    SUM(amount) AS total_sales,
    AVG(amount) AS avg_order
FROM transaction
WHERE type = 'SalesOrd'
  AND trandate >= ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -24)  -- Last 2 years
GROUP BY
    TO_CHAR(trandate, 'YYYY-MM'),
    EXTRACT(YEAR FROM trandate),
    EXTRACT(MONTH FROM trandate),
    TO_CHAR(trandate, 'Month')
ORDER BY year DESC, month_num

Test Case: Fiscal Period Handling

-- Sales by accounting period
SELECT
    ap.periodname,
    ap.startdate,
    ap.enddate,
    COUNT(t.id) AS transaction_count,
    SUM(t.amount) AS period_total
FROM accountingperiod ap
LEFT JOIN transaction t ON t.postingperiod = ap.id
WHERE ap.isyear = 'F'
  AND ap.isquarter = 'F'
  AND ap.startdate >= ADD_MONTHS(SYSDATE, -12)
GROUP BY ap.id, ap.periodname, ap.startdate, ap.enddate
ORDER BY ap.startdate

Test Case: Aging Buckets

-- Invoice aging analysis
SELECT
    c.companyname,
    t.tranid,
    t.duedate,
    t.amountremaining,
    ROUND(SYSDATE - t.duedate) AS days_overdue,
    CASE
        WHEN SYSDATE - t.duedate <= 0 THEN 'Current'
        WHEN SYSDATE - t.duedate BETWEEN 1 AND 30 THEN '1-30 Days'
        WHEN SYSDATE - t.duedate BETWEEN 31 AND 60 THEN '31-60 Days'
        WHEN SYSDATE - t.duedate BETWEEN 61 AND 90 THEN '61-90 Days'
        ELSE '90+ Days'
    END AS aging_bucket
FROM transaction t
INNER JOIN customer c ON t.entity = c.id
WHERE t.type = 'CustInvc'
  AND t.amountremaining > 0
ORDER BY days_overdue DESC

Test Case: Day-of-Week Analysis

-- Sales by day of week
SELECT
    TO_CHAR(trandate, 'Day') AS day_name,
    TO_CHAR(trandate, 'D') AS day_number,
    COUNT(*) AS order_count,
    SUM(amount) AS total_sales,
    ROUND(AVG(amount), 2) AS avg_order
FROM transaction
WHERE type = 'SalesOrd'
  AND trandate >= ADD_MONTHS(SYSDATE, -3)
GROUP BY TO_CHAR(trandate, 'Day'), TO_CHAR(trandate, 'D')
ORDER BY TO_CHAR(trandate, 'D')

---

Hierarchical Queries

Query parent-child relationships.

Employee Org Chart

-- Recursive CTE for employee hierarchy
WITH RECURSIVE EmpHierarchy AS (
    -- Top level (CEO, no supervisor)
    SELECT
        id,
        entityid AS name,
        title,
        supervisor,
        1 AS level,
        CAST(entityid AS VARCHAR(1000)) AS path
    FROM employee
    WHERE supervisor IS NULL
      AND isinactive = 'F'

    UNION ALL

    -- Subordinates
    SELECT
        e.id,
        e.entityid,
        e.title,
        e.supervisor,
        eh.level + 1,
        CAST(eh.path || ' > ' || e.entityid AS VARCHAR(1000))
    FROM employee e
    INNER JOIN EmpHierarchy eh ON e.supervisor = eh.id
    WHERE e.isinactive = 'F'
)
SELECT
    LPAD('  ', (level - 1) * 2) || name AS employee,
    title,
    level,
    path
FROM EmpHierarchy
ORDER BY path

Sample Result:

employee	title	level	path
John CEO	CEO	1	John CEO
··Jane VP	VP Sales	2	John CEO > Jane VP
····Bob Mgr	Sales Manager	3	John CEO > Jane VP > Bob Mgr
······Alice Rep	Sales Rep	4	John CEO > Jane VP > Bob Mgr > Alice Rep

Item BOM Explosion

-- Bill of Materials hierarchy
WITH RECURSIVE BOM AS (
    -- Top level assembly
    SELECT
        ab.item AS parent_id,
        ab.component AS component_id,
        ab.quantity,
        1 AS level,
        i.itemid AS path
    FROM assemblyitembom ab
    INNER JOIN item i ON ab.item = i.id
    WHERE ab.item = 12345  -- Starting assembly

    UNION ALL

    -- Sub-components
    SELECT
        ab.item,
        ab.component,
        ab.quantity * b.quantity,  -- Multiply quantities
        b.level + 1,
        b.path || ' > ' || i.itemid
    FROM assemblyitembom ab
    INNER JOIN BOM b ON ab.item = b.component_id
    INNER JOIN item i ON ab.item = i.id
)
SELECT
    b.level,
    LPAD('  ', (b.level - 1) * 2) || i.itemid AS component,
    i.displayname,
    b.quantity AS total_qty,
    b.path
FROM BOM b
INNER JOIN item i ON b.component_id = i.id
ORDER BY b.path

Account Hierarchy

-- Chart of accounts hierarchy
WITH RECURSIVE AcctHierarchy AS (
    SELECT
        id,
        acctnumber,
        acctname,
        parent,
        1 AS level
    FROM account
    WHERE parent IS NULL

    UNION ALL

    SELECT
        a.id,
        a.acctnumber,
        a.acctname,
        a.parent,
        ah.level + 1
    FROM account a
    INNER JOIN AcctHierarchy ah ON a.parent = ah.id
)
SELECT
    LPAD('  ', (level - 1) * 2) || acctnumber AS account,
    acctname,
    level
FROM AcctHierarchy
ORDER BY acctnumber

---

Pivot & Unpivot Patterns

Transform rows to columns and vice versa.

Manual Pivot with CASE

-- Sales by month as columns (pivot)
SELECT
    c.companyname,
    SUM(CASE WHEN EXTRACT(MONTH FROM t.trandate) = 1 THEN t.amount ELSE 0 END) AS jan,
    SUM(CASE WHEN EXTRACT(MONTH FROM t.trandate) = 2 THEN t.amount ELSE 0 END) AS feb,
    SUM(CASE WHEN EXTRACT(MONTH FROM t.trandate) = 3 THEN t.amount ELSE 0 END) AS mar,
    SUM(CASE WHEN EXTRACT(MONTH FROM t.trandate) = 4 THEN t.amount ELSE 0 END) AS apr,
    SUM(CASE WHEN EXTRACT(MONTH FROM t.trandate) = 5 THEN t.amount ELSE 0 END) AS may,
    SUM(CASE WHEN EXTRACT(MONTH FROM t.trandate) = 6 THEN t.amount ELSE 0 END) AS jun,
    SUM(CASE WHEN EXTRACT(MONTH FROM t.trandate) = 7 THEN t.amount ELSE 0 END) AS jul,
    SUM(CASE WHEN EXTRACT(MONTH FROM t.trandate) = 8 THEN t.amount ELSE 0 END) AS aug,
    SUM(CASE WHEN EXTRACT(MONTH FROM t.trandate) = 9 THEN t.amount ELSE 0 END) AS sep,
    SUM(CASE WHEN EXTRACT(MONTH FROM t.trandate) = 10 THEN t.amount ELSE 0 END) AS oct,
    SUM(CASE WHEN EXTRACT(MONTH FROM t.trandate) = 11 THEN t.amount ELSE 0 END) AS nov,
    SUM(CASE WHEN EXTRACT(MONTH FROM t.trandate) = 12 THEN t.amount ELSE 0 END) AS dec,
    SUM(t.amount) AS total
FROM transaction t
INNER JOIN customer c ON t.entity = c.id
WHERE t.type = 'SalesOrd'
  AND EXTRACT(YEAR FROM t.trandate) = 2024
GROUP BY c.id, c.companyname
ORDER BY total DESC
LIMIT 20

Test Case: Order Status Pivot

-- Orders by status as columns
SELECT
    e.entityid AS sales_rep,
    COUNT(CASE WHEN t.status = 'A' THEN 1 END) AS pending,
    COUNT(CASE WHEN t.status = 'B' THEN 1 END) AS approved,
    COUNT(CASE WHEN t.status = 'C' THEN 1 END) AS closed,
    COUNT(CASE WHEN t.status = 'D' THEN 1 END) AS cancelled,
    COUNT(*) AS total
FROM transaction t
INNER JOIN employee e ON t.salesrep = e.id
WHERE t.type = 'SalesOrd'
  AND t.trandate >= TRUNC(SYSDATE, 'YYYY')
GROUP BY e.id, e.entityid
ORDER BY total DESC

Sample Result:

sales_rep	pending	approved	closed	cancelled	total
John Smith	5	12	45	3	65
Jane Doe	3	8	52	2	65
Bob Wilson	8	5	38	5	56

Unpivot Pattern

Transform columns to rows:

-- Convert column values to rows
WITH CustomerMetrics AS (
    SELECT
        id,
        companyname,
        balance,
        creditlimit,
        daysoverdue
    FROM customer
    WHERE id = 123
)
SELECT
    companyname,
    metric_name,
    metric_value
FROM CustomerMetrics
CROSS JOIN (
    SELECT 'Balance' AS metric_name FROM DUAL
    UNION ALL SELECT 'Credit Limit' FROM DUAL
    UNION ALL SELECT 'Days Overdue' FROM DUAL
) metrics
CROSS JOIN (
    SELECT
        balance AS val1,
        creditlimit AS val2,
        daysoverdue AS val3
    FROM CustomerMetrics
) vals
WHERE (metric_name = 'Balance' AND metric_value = val1)
   OR (metric_name = 'Credit Limit' AND metric_value = val2)
   OR (metric_name = 'Days Overdue' AND metric_value = val3)

---

Advanced Aggregations

ROLLUP for Subtotals

-- Sales with subtotals and grand total
SELECT
    COALESCE(c.companyname, '** ALL CUSTOMERS **') AS customer,
    COALESCE(TO_CHAR(t.trandate, 'YYYY-MM'), '** ALL MONTHS **') AS month,
    SUM(t.amount) AS sales
FROM transaction t
INNER JOIN customer c ON t.entity = c.id
WHERE t.type = 'SalesOrd'
  AND t.trandate >= TRUNC(SYSDATE, 'YYYY')
GROUP BY ROLLUP(c.companyname, TO_CHAR(t.trandate, 'YYYY-MM'))
ORDER BY
    CASE WHEN c.companyname IS NULL THEN 1 ELSE 0 END,
    c.companyname,
    CASE WHEN TO_CHAR(t.trandate, 'YYYY-MM') IS NULL THEN 1 ELSE 0 END,
    month

Sample Result:

customer	month	sales
Acme Corp	2024-01	15,000
Acme Corp	2024-02	18,000
Acme Corp	** ALL MONTHS **	33,000
Beta Inc	2024-01	12,000
Beta Inc	2024-02	14,000
Beta Inc	** ALL MONTHS **	26,000
** ALL CUSTOMERS **	** ALL MONTHS **	59,000

Percentage of Total

-- Each customer's percentage of total sales
WITH TotalSales AS (
    SELECT SUM(amount) AS grand_total
    FROM transaction
    WHERE type = 'SalesOrd'
      AND trandate >= TRUNC(SYSDATE, 'YYYY')
)
SELECT
    c.companyname,
    SUM(t.amount) AS customer_sales,
    ts.grand_total,
    ROUND(SUM(t.amount) / ts.grand_total * 100, 2) AS pct_of_total,
    SUM(SUM(t.amount)) OVER (ORDER BY SUM(t.amount) DESC) AS running_total,
    ROUND(
        SUM(SUM(t.amount)) OVER (ORDER BY SUM(t.amount) DESC) / ts.grand_total * 100,
        2
    ) AS cumulative_pct
FROM transaction t
INNER JOIN customer c ON t.entity = c.id
CROSS JOIN TotalSales ts
WHERE t.type = 'SalesOrd'
  AND t.trandate >= TRUNC(SYSDATE, 'YYYY')
GROUP BY c.id, c.companyname, ts.grand_total
ORDER BY customer_sales DESC

Moving Averages

-- 7-day moving average of daily sales
WITH DailySales AS (
    SELECT
        TRUNC(trandate) AS sale_date,
        SUM(amount) AS daily_total
    FROM transaction
    WHERE type = 'SalesOrd'
      AND trandate >= SYSDATE - 60
    GROUP BY TRUNC(trandate)
)
SELECT
    sale_date,
    daily_total,
    ROUND(
        AVG(daily_total) OVER (
            ORDER BY sale_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_7day,
    ROUND(
        AVG(daily_total) OVER (
            ORDER BY sale_date
            ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_30day
FROM DailySales
ORDER BY sale_date

Year-over-Year Comparison

-- Monthly YoY comparison
WITH MonthlySales AS (
    SELECT
        EXTRACT(YEAR FROM trandate) AS year,
        EXTRACT(MONTH FROM trandate) AS month,
        SUM(amount) AS sales
    FROM transaction
    WHERE type = 'SalesOrd'
      AND trandate >= ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -24)
    GROUP BY EXTRACT(YEAR FROM trandate), EXTRACT(MONTH FROM trandate)
)
SELECT
    curr.month,
    TO_CHAR(TO_DATE(curr.month, 'MM'), 'Month') AS month_name,
    curr.sales AS current_year,
    prev.sales AS prior_year,
    curr.sales - NVL(prev.sales, 0) AS variance,
    ROUND(
        CASE WHEN prev.sales > 0
        THEN (curr.sales - prev.sales) / prev.sales * 100
        ELSE 0 END,
        2
    ) AS yoy_growth_pct
FROM MonthlySales curr
LEFT JOIN MonthlySales prev ON curr.month = prev.month
                            AND curr.year = prev.year + 1
WHERE curr.year = EXTRACT(YEAR FROM SYSDATE)
ORDER BY curr.month

Quartile Analysis

-- Customer distribution by sales quartile
WITH CustomerSales AS (
    SELECT
        c.id,
        c.companyname,
        SUM(t.amount) AS total_sales,
        NTILE(4) OVER (ORDER BY SUM(t.amount)) AS quartile
    FROM customer c
    LEFT JOIN transaction t ON c.id = t.entity AND t.type = 'SalesOrd'
    WHERE c.isinactive = 'F'
    GROUP BY c.id, c.companyname
)
SELECT
    quartile,
    COUNT(*) AS customer_count,
    MIN(total_sales) AS min_sales,
    MAX(total_sales) AS max_sales,
    ROUND(AVG(total_sales), 2) AS avg_sales,
    SUM(total_sales) AS total_sales
FROM CustomerSales
GROUP BY quartile
ORDER BY quartile

---

Quick Reference

Date Shortcuts

-- Common date expressions
TRUNC(SYSDATE, 'MM')           -- First of current month
ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)  -- First of last month
LAST_DAY(SYSDATE)              -- Last day of current month
TRUNC(SYSDATE, 'YYYY')         -- First of current year
SYSDATE - 30                   -- 30 days ago

Pivot Template

SELECT grouping_column,
    SUM(CASE WHEN pivot_column = 'value1' THEN amount ELSE 0 END) AS col1,
    SUM(CASE WHEN pivot_column = 'value2' THEN amount ELSE 0 END) AS col2
FROM table
GROUP BY grouping_column

Hierarchy Template

WITH RECURSIVE Hierarchy AS (
    SELECT id, name, parent, 1 AS level FROM table WHERE parent IS NULL
    UNION ALL
    SELECT t.id, t.name, t.parent, h.level + 1
    FROM table t INNER JOIN Hierarchy h ON t.parent = h.id
)
SELECT * FROM Hierarchy