Quality

ComponentSchema

---

CMS

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


CREATE 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 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