business_logic_checks.sql

-- ============================================================
-- Script  : healthcare_claims_validation.sql
-- Purpose : Business logic validation for Healthcare domain
-- Author  : Arunkumar Aravindhakshan
-- Tools   : Snowflake / Oracle
-- ============================================================

-- -------------------------------------------------------
-- 1. CLAIM AMOUNT RANGE VALIDATION
--    Flags claims with zero, negative, or unusually high amounts
-- -------------------------------------------------------
SELECT
    claim_id,
    patient_id,
    claim_amount,
    CASE
        WHEN claim_amount <= 0              THEN 'FAIL — Zero or Negative Amount'
        WHEN claim_amount > 1000000         THEN 'FAIL — Amount Exceeds Threshold'
        ELSE 'PASS'
    END AS amount_validation
FROM <target_schema>.claims_target
WHERE claim_amount <= 0 OR claim_amount > 1000000;


-- -------------------------------------------------------
-- 2. DATE LOGIC VALIDATION
--    Discharge date must be >= admission date
-- -------------------------------------------------------
SELECT
    claim_id,
    patient_id,
    admission_date,
    discharge_date,
    DATEDIFF(day, admission_date, discharge_date) AS length_of_stay,
    CASE
        WHEN discharge_date < admission_date THEN 'FAIL — Discharge before Admission'
        WHEN discharge_date = admission_date THEN 'WARN — Same day discharge'
        ELSE 'PASS'
    END AS date_validation
FROM <target_schema>.claims_target
WHERE discharge_date < admission_date;


-- -------------------------------------------------------
-- 3. DIAGNOSIS CODE FORMAT VALIDATION (ICD-10)
--    ICD-10 codes must follow pattern: Letter + 2 digits + optional decimal
-- -------------------------------------------------------
SELECT
    claim_id,
    diagnosis_code,
    CASE
        WHEN diagnosis_code REGEXP '^[A-Z][0-9]{2}(\.[0-9]{1,4})?$' THEN 'PASS'
        ELSE 'FAIL — Invalid ICD-10 Format'
    END AS diagnosis_validation
FROM <target_schema>.claims_target
WHERE NOT (diagnosis_code REGEXP '^[A-Z][0-9]{2}(\.[0-9]{1,4})?$');


-- -------------------------------------------------------
-- 4. PROVIDER REFERENTIAL INTEGRITY
--    Every claim must have a valid provider in provider master
-- -------------------------------------------------------
SELECT
    c.claim_id,
    c.provider_id,
    'FAIL — Provider not in master' AS issue
FROM <target_schema>.claims_target c
LEFT JOIN <target_schema>.provider_master p
    ON c.provider_id = p.provider_id
WHERE p.provider_id IS NULL;


-- ============================================================
-- Script  : banking_transaction_checks.sql
-- Purpose : Business logic validation for Banking domain
-- ============================================================

-- -------------------------------------------------------
-- 5. DEBIT/CREDIT BALANCE CHECK
--    Total debits must equal total credits per account
-- -------------------------------------------------------
SELECT
    account_id,
    SUM(CASE WHEN transaction_type = 'DEBIT'  THEN amount ELSE 0 END) AS total_debits,
    SUM(CASE WHEN transaction_type = 'CREDIT' THEN amount ELSE 0 END) AS total_credits,
    SUM(CASE WHEN transaction_type = 'DEBIT'  THEN amount ELSE 0 END) -
    SUM(CASE WHEN transaction_type = 'CREDIT' THEN amount ELSE 0 END) AS balance_difference,
    CASE
        WHEN ABS(
            SUM(CASE WHEN transaction_type = 'DEBIT'  THEN amount ELSE 0 END) -
            SUM(CASE WHEN transaction_type = 'CREDIT' THEN amount ELSE 0 END)
        ) < 0.01 THEN 'PASS'
        ELSE 'FAIL — Balance Mismatch'
    END AS balance_status
FROM <target_schema>.transactions_target
GROUP BY account_id
HAVING ABS(
    SUM(CASE WHEN transaction_type = 'DEBIT'  THEN amount ELSE 0 END) -
    SUM(CASE WHEN transaction_type = 'CREDIT' THEN amount ELSE 0 END)
) >= 0.01;


-- -------------------------------------------------------
-- 6. DUPLICATE TRANSACTION DETECTION
--    Same account, amount, and timestamp = likely duplicate
-- -------------------------------------------------------
SELECT
    account_id,
    transaction_date,
    amount,
    transaction_type,
    COUNT(*) AS occurrence_count
FROM <target_schema>.transactions_target
GROUP BY account_id, transaction_date, amount, transaction_type
HAVING COUNT(*) > 1;


-- ============================================================
-- Script  : hospitality_revenue_validation.sql
-- Purpose : Business logic validation for Hospitality domain
-- ============================================================

-- -------------------------------------------------------
-- 7. ROOM REVENUE VALIDATION
--    Revenue = room_rate * nights_stayed must match total_revenue
-- -------------------------------------------------------
SELECT
    booking_id,
    room_rate,
    nights_stayed,
    (room_rate * nights_stayed)     AS expected_revenue,
    total_revenue                   AS actual_revenue,
    ABS((room_rate * nights_stayed) - total_revenue) AS variance,
    CASE
        WHEN ABS((room_rate * nights_stayed) - total_revenue) < 0.01 THEN 'PASS'
        ELSE 'FAIL — Revenue Calculation Mismatch'
    END AS revenue_validation
FROM <target_schema>.bookings_target
WHERE ABS((room_rate * nights_stayed) - total_revenue) >= 0.01;


-- -------------------------------------------------------
-- 8. OCCUPANCY RATE VALIDATION
--    Occupancy % = booked_rooms / total_rooms * 100
-- -------------------------------------------------------
SELECT
    property_id,
    report_date,
    booked_rooms,
    total_rooms,
    ROUND((booked_rooms * 100.0 / NULLIF(total_rooms, 0)), 2) AS expected_occupancy_pct,
    reported_occupancy_pct,
    CASE
        WHEN ABS(
            ROUND((booked_rooms * 100.0 / NULLIF(total_rooms, 0)), 2) - reported_occupancy_pct
        ) < 0.1 THEN 'PASS'
        ELSE 'FAIL — Occupancy % Mismatch'
    END AS occupancy_validation
FROM <target_schema>.property_metrics
ORDER BY report_date DESC;