Quality

ComponentSchema

---

CMS

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_quality_cms_{{ sub_version }} 
-- Subversion: {{ sub_version }}


CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_quality_cms_{{ sub_version }} AS 
SELECT 
    provider_id, 
    max(provider_quality_rating) as hospital_overall_rating
FROM {{ cld_params.Tables.SPINES_PROVIDER_HOSPITAL.value }}
GROUP BY provider_id

Leapfrog

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_quality_leapfrog_{{ sub_version }} 
-- Subversion: {{ sub_version }}


-- leapfrog's data can be more granular than "provider_id"
-- e.g. leapfrog lists "Slidell Memorial Hospital" and 
-- "Slidell Memorial Hospital East" separately
-- take the max score and min grade
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_quality_leapfrog_{{ sub_version }} AS 
SELECT 
    CAST(upt.provider_id AS VARCHAR) as provider_id,
    max(l.hospital_score) as hospital_score,
    min(l.hospital_grade) as hospital_grade
FROM {{ cld_params.Tables.LEAPFROG_HOSPITAL_RATINGS.value }} l
JOIN {{ cld_params.Tables.UNIFIED_PROVIDER_TABLE.value }} upt
    ON upt.provider_npi = l.npi
WHERE upt.provider_id IS NOT NULL
AND l.hospital_score IS NOT NULL
GROUP BY 1

ASC Plausibility

CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_asc_plausibility_{{ sub_version }} AS
WITH
-- ASC-specific plausibility (SE-12773)
-- Maps each ASC provider to the specific codes they can perform based on AMSURG specialty data
-- Uses AMSURG Medicare Certified ASC specialty mappings as the primary source with spines fallback
-- Updated to use service_line from code spine for direct mapping (no clinical_categories_index dependency)
asc_plausibility AS (
    -- AMSURG-based plausibility (using service_line from code spine)
    SELECT DISTINCT
        sp.provider_id,
        cs.bill_type,
        cs.billing_code_type,
        cs.billing_code,
        cs.facility,
        True as is_plausible
    FROM {{ cld_params.Tables.SPINES_PROVIDER_ASC.value }} sp
    INNER JOIN {{ cld_params.Tables.AMSURG_ASC_PROVIDER_TYPES.value }} amsurg
        ON sp.npi = amsurg.npi
        AND amsurg.specialty != 'Multispecialty'  -- Handle multispecialty separately
        AND amsurg.specialty != '-'  -- Skip invalid entries
    CROSS JOIN {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} cs
    WHERE cs.bill_type = 'Outpatient'
        AND cs.facility IS NULL  -- ASC codes are always NULL for facility
        AND (
            -- Direct specialty-based filtering using service_line
            (amsurg.specialty IN ('Dental') AND cs.service_line = 'Dental') OR
            (amsurg.specialty IN ('Dermatology') AND cs.service_line = 'Dermatology') OR
            (amsurg.specialty IN ('Gastroenterology') AND cs.service_line = 'Gastrointestinal') OR
            (amsurg.specialty IN ('General Surgery', 'Plastic Surgery', 'Bariatrics') AND cs.service_line IN ('Surgery', 'General Surgery')) OR
            (amsurg.specialty IN ('Gynecology') AND cs.service_line = 'Reproductive') OR
            (amsurg.specialty IN ('Ophthalmology', 'Oculoplastics', 'Retina') AND cs.service_line = 'Ophthalmology') OR
            (amsurg.specialty IN ('Orthopedic Surgery', 'Pain Management', 'Podiatry', 'Spine') AND cs.service_line = 'MSK') OR
            (amsurg.specialty IN ('Urology') AND cs.service_line = 'Urology') OR
            (amsurg.specialty IN ('Cardiovascular/Cardiology/Cardiac Cath', 'Vascular Surgery') AND cs.service_line = 'Cardiovascular') OR
            (amsurg.specialty IN ('Otolaryngology') AND cs.service_line = 'ENT') OR
            (amsurg.specialty IN ('Colon & Rectal Surgery') AND cs.service_line = 'Gastrointestinal')
        )
        AND cs.billing_code IN (
            SELECT billing_code
            FROM {{ cld_params.Tables.ASC_CODESET.value }}
        )
    
    UNION
    
    -- Fallback: Use spines binary service flags for ASCs not in AMSURG data
    SELECT DISTINCT
        sp.provider_id,
        cs.bill_type,
        cs.billing_code_type,
        cs.billing_code,
        cs.facility,
        True as is_plausible
    FROM {{ cld_params.Tables.SPINES_PROVIDER_ASC.value }} sp
    LEFT JOIN {{ cld_params.Tables.AMSURG_ASC_PROVIDER_TYPES.value }} amsurg
        ON sp.npi = amsurg.npi
    CROSS JOIN {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} cs
    WHERE cs.bill_type = 'Outpatient'
        AND cs.facility IS NULL
        AND amsurg.npi IS NULL  -- Only for ASCs not in AMSURG data
        AND (
            (sp.services_lab = 1 AND cs.service_line = 'Lab/Path') OR
            (sp.services_radiology = 1 AND cs.service_line = 'Radiology') OR
            (sp.services_cardiology = 1 AND cs.service_line = 'Cardiovascular') OR
            (sp.services_dental = 1 AND cs.service_line = 'Dental') OR
            (sp.services_dermatology = 1 AND cs.service_line = 'Dermatology') OR
            (sp.services_ent = 1 AND cs.service_line = 'ENT') OR
            (sp.services_general_other = 1 AND cs.service_line = 'Surgery') OR
            (sp.services_endoscopy = 1 AND cs.service_line = 'Gastrointestinal') OR
            (sp.services_nephrology = 1 AND cs.service_line = 'Nephrology') OR
            (sp.services_neurology = 1 AND cs.service_line = 'Neurology') OR
            (sp.services_obgyn = 1 AND cs.service_line = 'Reproductive') OR
            (sp.services_opthalmology = 1 AND cs.service_line = 'Ophthalmology') OR
            (sp.services_orthopedic = 1 AND cs.service_line = 'MSK') OR
            (sp.services_pain = 1 AND cs.service_line = 'MSK') OR
            (sp.services_plastic_surgery = 1 AND cs.service_line = 'Surgery') OR
            (sp.services_podiatry = 1 AND cs.service_line = 'MSK') OR
            (sp.services_pulmonary = 1 AND cs.service_line = 'Pulmonology') OR
            (sp.services_urology = 1 AND cs.service_line = 'Urology')
        )
        AND cs.billing_code IN (
            SELECT billing_code
            FROM {{ cld_params.Tables.ASC_CODESET.value }}
        )
    
    UNION 
    -- Multispecialty ASCs (or unknown) get all ASC service lines
    SELECT DISTINCT
        sp.provider_id,
        cs.bill_type,
        cs.billing_code_type,
        cs.billing_code,
        cs.facility,
        True as is_plausible
    FROM {{ cld_params.Tables.SPINES_PROVIDER_ASC.value }} sp
    LEFT JOIN {{ cld_params.Tables.AMSURG_ASC_PROVIDER_TYPES.value }} amsurg
        ON sp.npi = amsurg.npi
    CROSS JOIN {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} cs
    WHERE cs.bill_type = 'Outpatient'
        AND cs.facility IS NULL
        AND cs.service_line IN (
            'Surgery', 'MSK', 'Gastrointestinal', 'Ophthalmology', 'Urology', 'Cardiovascular',
            'Radiology', 'Lab/Path', 'Dermatology', 'ENT', 'Reproductive', 'Nephrology',
            'Neurology', 'Pulmonology', 'Dental'
        )
        AND (
            amsurg.specialty = 'Multispecialty'
            OR
            (
                sp.services_lab = 0 AND
                sp.services_radiology = 0 AND
                sp.services_cardiology = 0 AND
                sp.services_dental = 0 AND
                sp.services_dermatology = 0 AND
                sp.services_ent = 0 AND
                sp.services_general_other = 0 AND
                sp.services_endoscopy = 0 AND
                sp.services_nephrology = 0 AND
                sp.services_neurology = 0 AND
                sp.services_obgyn = 0 AND
                sp.services_opthalmology = 0 AND
                sp.services_orthopedic = 0 AND
                sp.services_pain = 0 AND
                sp.services_plastic_surgery = 0 AND
                sp.services_podiatry = 0 AND
                sp.services_pulmonary = 0 AND
                sp.services_urology = 0
            )
        )
        AND cs.billing_code IN (
            SELECT billing_code
            FROM {{ cld_params.Tables.ASC_CODESET.value }}
        )
)
SELECT *
FROM asc_plausibility