code

Here’s a SQL query you can use to verify and analyze the difference between the two columns gas_loss_rate and gas_loss_rate_v2 in both tables — ECKERNEL_WST.TV_WELL_DEFERMENT and ECKERNEL_GORGON.TV_WELL_DEFERMENT:

---

✅ Option 1: Check overall differences and stats

-- Summary comparison for both schemas
SELECT
    'ECKERNEL_WST' AS SOURCE,
    COUNT(*) AS TOTAL_RECORDS,
    SUM(CASE WHEN gas_loss_rate <> gas_loss_rate_v2 THEN 1 ELSE 0 END) AS MISMATCH_COUNT,
    ROUND(100.0 * SUM(CASE WHEN gas_loss_rate <> gas_loss_rate_v2 THEN 1 ELSE 0 END) / COUNT(*), 2) AS PCT_MISMATCH,
    MIN(gas_loss_rate - gas_loss_rate_v2) AS MIN_DIFF,
    MAX(gas_loss_rate - gas_loss_rate_v2) AS MAX_DIFF,
    AVG(ABS(gas_loss_rate - gas_loss_rate_v2)) AS AVG_ABS_DIFF
FROM ECKERNEL_WST.TV_WELL_DEFERMENT
UNION ALL
SELECT
    'ECKERNEL_GORGON' AS SOURCE,
    COUNT(*) AS TOTAL_RECORDS,
    SUM(CASE WHEN gas_loss_rate <> gas_loss_rate_v2 THEN 1 ELSE 0 END) AS MISMATCH_COUNT,
    ROUND(100.0 * SUM(CASE WHEN gas_loss_rate <> gas_loss_rate_v2 THEN 1 ELSE 0 END) / COUNT(*), 2) AS PCT_MISMATCH,
    MIN(gas_loss_rate - gas_loss_rate_v2) AS MIN_DIFF,
    MAX(gas_loss_rate - gas_loss_rate_v2) AS MAX_DIFF,
    AVG(ABS(gas_loss_rate - gas_loss_rate_v2)) AS AVG_ABS_DIFF
FROM ECKERNEL_GORGON.TV_WELL_DEFERMENT;

🟢 What this does:

• Compares the two columns in both tables.

• Shows how many records differ and the magnitude of the difference.

• Helps you quickly see if mismatches are widespread or minor.

---

✅ Option 2: Get record-level mismatches

-- Detailed record-level differences (sample)
SELECT
    WELL_UI_ID,
    gas_loss_rate,
    gas_loss_rate_v2,
    (gas_loss_rate - gas_loss_rate_v2) AS DIFF,
    UPDATE_DATE,
    STATUS
FROM ECKERNEL_WST.TV_WELL_DEFERMENT
WHERE gas_loss_rate <> gas_loss_rate_v2
ORDER BY ABS(gas_loss_rate - gas_loss_rate_v2) DESC;

🟢 Tip:
If the key column differs (e.g. WELL_UI_ID or WELL_CODE), replace it accordingly.

---

✅ Option 3: Compare across schemas for the same well (if applicable)

If the same wells exist in both schemas (ECKERNEL_WST and ECKERNEL_GORGON), you can compare the two sources directly:

SELECT
    COALESCE(a.WELL_UI_ID, b.WELL_UI_ID) AS WELL_UI_ID,
    a.gas_loss_rate AS WST_GAS_LOSS,
    a.gas_loss_rate_v2 AS WST_GAS_LOSS_V2,
    b.gas_loss_rate AS GORGON_GAS_LOSS,
    b.gas_loss_rate_v2 AS GORGON_GAS_LOSS_V2
FROM ECKERNEL_WST.TV_WELL_DEFERMENT a
FULL OUTER JOIN ECKERNEL_GORGON.TV_WELL_DEFERMENT b
    ON a.WELL_UI_ID = b.WELL_UI_ID
WHERE 
    (a.gas_loss_rate <> b.gas_loss_rate)
    OR (a.gas_loss_rate_v2 <> b.gas_loss_rate_v2);

---

Would you like me to include a threshold filter (e.g., show only mismatches where the absolute difference > 10%) to focus on significant deviations?