ERP Analytics Views — SQL Server

Set of analytical SQL views over ERP transactional tables for year-over-year sales comparisons, client retention analysis, and commission reconciliation.

SQL ServerSage 300T-SQLERP

Architecture

Raw Schema

→

Views / CTEs

→

Aggregations

→

Consumer

SQL Snippet

-- Year-over-year sales comparison view
CREATE VIEW dbo.vw_SalesYoY AS
SELECT
    h.ORDDATE                           AS order_date,
    h.CUSTOMER                          AS customer_id,
    d.ITEM                              AS item_code,
    SUM(d.QTYSHIPPED * d.UNITPRICE)     AS revenue_current,
    LAG(SUM(d.QTYSHIPPED * d.UNITPRICE))
        OVER (PARTITION BY d.ITEM
              ORDER BY YEAR(h.ORDDATE)) AS revenue_prior_year
FROM OEINVH h
JOIN OEINVD d ON d.INVUNIQ = h.INVUNIQ
GROUP BY h.ORDDATE, h.CUSTOMER, d.ITEM;

Detailed write-up, screenshots, and metrics coming in Phase 4.