Transform Your
Sql Server Analytics

Use Case:

"Create a comprehensive sales analysis dashboard"

Solution:

                        
WITH sales_metrics AS (
    SELECT 
        DATEADD(MONTH, DATEDIFF(MONTH, 0, s.OrderDate), 0) AS sale_month,
        p.CategoryID,
        c.CategoryName,
        SUM(s.Quantity * s.UnitPrice) as revenue,
        COUNT(DISTINCT s.OrderID) as order_count,
        COUNT(DISTINCT s.CustomerID) as customer_count,
        SUM(s.Quantity) as units_sold
    FROM 
        Sales s
        JOIN Products p ON s.ProductID = p.ProductID
        JOIN Categories c ON p.CategoryID = c.CategoryID
    WHERE 
        s.OrderDate >= DATEADD(MONTH, -12, GETDATE())
    GROUP BY 
        DATEADD(MONTH, DATEDIFF(MONTH, 0, s.OrderDate), 0),
        p.CategoryID,
        c.CategoryName
),
category_growth AS (
    SELECT 
        *,
        LAG(revenue) OVER (PARTITION BY CategoryID ORDER BY sale_month) as prev_month_revenue,
        CASE 
            WHEN LAG(revenue) OVER (PARTITION BY CategoryID ORDER BY sale_month) > 0
            THEN ((revenue - LAG(revenue) OVER (PARTITION BY CategoryID ORDER BY sale_month)) / 
                  LAG(revenue) OVER (PARTITION BY CategoryID ORDER BY sale_month)) * 100
            ELSE 0
        END as growth_percentage
    FROM sales_metrics
)
SELECT 
    sale_month,
    CategoryName,
    revenue,
    order_count,
    customer_count,
    units_sold,
    growth_percentage,
    FIRST_VALUE(revenue) OVER (PARTITION BY CategoryID ORDER BY sale_month DESC) as latest_revenue,
    AVG(revenue) OVER (PARTITION BY CategoryID ORDER BY sale_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg_revenue
FROM category_growth
ORDER BY 
    CategoryName,
    sale_month;
                    

Use Case:

"Monitor real-time performance with temporal tables"

Solution:

                        
-- Enable system versioning
ALTER TABLE Products
ADD 
    ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT df_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
    ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT df_ValidTo DEFAULT '9999-12-31 23:59:59.9999999',
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE Products
SET (SYSTEM_VERSIONING = ON);

-- Query for price change analysis
SELECT 
    p.ProductName,
    p.UnitPrice as current_price,
    ph.UnitPrice as historical_price,
    p.ValidFrom as price_change_date,
    DATEDIFF(DAY, ph.ValidFrom, p.ValidFrom) as days_at_previous_price,
    ((p.UnitPrice - ph.UnitPrice) / ph.UnitPrice * 100) as price_change_percentage
FROM 
    Products p
    CROSS APPLY (
        SELECT TOP 1 history.UnitPrice, history.ValidFrom
        FROM Products FOR SYSTEM_TIME ALL as history
        WHERE history.ProductID = p.ProductID
            AND history.ValidTo < p.ValidFrom
        ORDER BY history.ValidFrom DESC
    ) ph
WHERE 
    p.UnitPrice <> ph.UnitPrice
ORDER BY 
    price_change_percentage DESC;