JT

ery to Find C_Kwh Decrease (Discrepancies)

WITH Ranked AS (
    SELECT 
        Price_ID,
        Term,
        C_Kwh,
        ROW_NUMBER() OVER (PARTITION BY Price_ID ORDER BY Term ASC)  AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY Price_ID ORDER BY Term DESC) AS rn_desc
    FROM PricingTable
),
FirstLast AS (
    SELECT 
        Price_ID,
        MAX(CASE WHEN rn_asc = 1 THEN Term END) AS FirstTerm,
        MAX(CASE WHEN rn_desc = 1 THEN Term END) AS LastTerm,
        MAX(CASE WHEN rn_asc = 1 THEN C_Kwh END) AS First_C_Kwh,
        MAX(CASE WHEN rn_desc = 1 THEN C_Kwh END) AS Last_C_Kwh
    FROM Ranked
    GROUP BY Price_ID
)
SELECT
    Price_ID,
    FirstTerm,
    First_C_Kwh,
    LastTerm,
    Last_C_Kwh,
    CASE 
        WHEN Last_C_Kwh > First_C_Kwh THEN '✅ Increased'
        WHEN Last_C_Kwh = First_C_Kwh THEN '⚠️ Same Value'
        WHEN Last_C_Kwh < First_C_Kwh THEN '❌ Decreased'
        ELSE 'N/A'
    END AS ComparisonResult
FROM FirstLast
ORDER BY Price_ID;


WITH Ranked AS (
    SELECT 
        Price_ID,
        Term,
        C_Kwh,
        ROW_NUMBER() OVER (PARTITION BY Price_ID ORDER BY Term ASC)  AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY Price_ID ORDER BY Term DESC) AS rn_desc
    FROM PricingTable
)
SELECT 
    Price_ID,
    Term,
    C_Kwh,
    CASE 
        WHEN rn_asc = 1 THEN 'First Term'
        WHEN rn_desc = 1 THEN 'Last Term'
    END AS TermPosition
FROM Ranked
WHERE rn_asc = 1 OR rn_desc = 1
ORDER BY Price_ID, Term;


SELECT 
    t1.Price_ID,
    t1.Term,
    t1.C_Kwh,
    t2.C_Kwh AS Prev_C_Kwh
FROM your_table_name t1
LEFT JOIN your_table_name t2 
    ON t1.Price_ID = t2.Price_ID
    AND t1.Term = t2.Term + 1
WHERE t2.C_Kwh IS NOT NULL
  AND t1.C_Kwh < t2.C_Kwh
ORDER BY t1.Price_ID, t1.Term;




WITH OrderedData AS (
    SELECT 
        Price_ID,
        Term,
        C_Kwh,
        LAG(C_Kwh) OVER (PARTITION BY Price_ID ORDER BY Term) AS Prev_C_Kwh
    FROM your_table_name
)
SELECT 
    Price_ID,
    Term,
    C_Kwh,
    Prev_C_Kwh
FROM OrderedData
WHERE Prev_C_Kwh IS NOT NULL
  AND C_Kwh < Prev_C_Kwh
ORDER BY Price_ID, Term;