Power BI Dashboard Technical Plan

-- Conceptual SQL for v_ChargeRecon_Fact View
CREATE OR REPLACE VIEW gold.v_ChargeRecon_Fact AS

WITH Appointments AS (
    SELECT EncounterID, AppointmentDate, PatientID, ProviderID, ClinicID
    FROM silver.appointments WHERE AppointmentStatus = 'Completed'
),

Charges AS (
    SELECT EncounterID, ChargePostDate, ChargeAmount, IsDenied, DenialReasonID
    FROM silver.transactions
),

FactData AS (
    SELECT
        a.EncounterID, a.PatientID, a.ProviderID, a.ClinicID,
        a.AppointmentDate, c.ChargePostDate, c.ChargeAmount,
        c.IsDenied, c.DenialReasonID,
        DATEDIFF(c.ChargePostDate, a.AppointmentDate) AS ChargeLagDays
    FROM Appointments a
    LEFT JOIN Charges c ON a.EncounterID = c.EncounterID
)

SELECT *,
    CASE WHEN ChargeLagDays > 7 THEN TRUE ELSE FALSE END AS IsLateCharge
FROM FactData;