Skip to main content
Version: 2.2

Network Check Gaps (Provider/Code)

Takeaways​

Clear Rates contains >87% of the providers and codes in the Employer NC data.

  • The majority of the provider gaps are from ambulance, DME, and clinics.
  • The majority of the code gaps are from ambulance codes, observation codes, and supplies.
  • There are some important gaps such as CAR-T and transplants DRGs that Clear Rates team is currently working on adding.

Recommendation: The Employer team should evaluate whether these gaps materially affect customers’ analyses and determine if maintaining a separate pipeline is justified by the associated costs.

warning

Note that this analysis considers whether the provider / code exists in the RATE OBJECT SPACE, not whether a rate exists for the specific service rendered.

Clear Rates coverage would be significantly lower if we considered whether a rate exists.

However, Clear Rates / Employer can add a layer to the Clear Rates data that adds Network Check imputation tiers that do not exist and will not be added to Clear Rates, such as APC-averages and geographic averages.

Providers​

Hospitals cover ~66% of the total cost, physician groups cover ~21%, and ASCs/imaging labs cover ~4%.

In total, ~87% of the total cost is covered by Clear Rates providers.

costpercentage
total_cost1.10541e+111
hospital7.31348e+100.661608
physician_group2.27597e+100.205894
asc_imaging_lab4.11716e+090.0372456
cost_covered9.58315e+100.866932
SQL
WITH 
counts AS (
    SELECT npi, sum(total_cost) as cost
    FROM tq_production.employernc_results.main_qc
    WHERE npi IS NOT NULL
    GROUP BY npi
)
SELECT 
    SUM(cost) as total_cost,
    SUM(
        CASE 
            WHEN p.npi IS NOT NULL OR spa.npi IS NOT NULL
            THEN cost ELSE 0 
        END
    ) as hospital,
    SUM(
        CASE 
            WHEN 
                def.npi IS NOT NULL 
                OR def_assoc.npi IS NOT NULL
                OR pecos.npi IS NOT NULL
            THEN cost ELSE 0 
        END
    ) as physician_group,
    SUM(
        CASE 
            WHEN asc_imaging_lab.npi IS NOT NULL
            THEN cost ELSE 0 
        END
    ) as asc_imaging_lab,
    SUM(CASE WHEN 
        p.npi IS NOT NULL OR 
        def.npi IS NOT NULL OR 
        spa.npi IS NOT NULL OR 
        def_assoc.npi IS NOT NULL OR 
        pecos.npi IS NOT NULL OR
        asc_imaging_lab.npi IS NOT NULL
    THEN cost ELSE 0 END) as cost_covered
FROM counts m
LEFT JOIN (
    SELECT DISTINCT npi 
    FROM tq_production.spines.spines_provider
) p 
    ON m.npi = p.npi
LEFT JOIN (
    SELECT DISTINCT npi 
    FROM tq_production.spines.spines_provider_hospitals_additional_npis
) spa
    ON m.npi = spa.npi
LEFT JOIN (
    SELECT DISTINCT npi 
    FROM tq_production.definitive_healthcare.physician_groups_physicians
) def
    ON m.npi = CAST(def.npi AS VARCHAR)
LEFT JOIN (
    SELECT DISTINCT npi_number as npi 
    FROM tq_production.definitive_healthcare.physician_groups_associated_npi_numbers
) def_assoc
    ON m.npi = CAST(def_assoc.npi AS VARCHAR)
LEFT JOIN (
    SELECT DISTINCT individual_npi as npi 
    FROM tq_dev.internal_dev_csong_graph.pecos_data_clean_provider_types_2025_07
) pecos
    ON m.npi = pecos.npi
LEFT JOIN (
    SELECT DISTINCT npi
    FROM tq_production.spines.spines_provider_ascs
    UNION 
    SELECT DISTINCT npi
    FROM tq_production.spines.spines_provider_imaging_centers
    UNION
    SELECT DISTINCT npi
    FROM tq_production.spines.spines_provider_ascs_additional_npis
    UNION 
    SELECT DISTINCT t.npi
    FROM tq_dev.internal_dev_csong_cld_v2_1_2.tmp_ref_provider_spine_2025_07, unnest(npi) as t(npi)
    WHERE provider_type LIKE '%Lab%'
) asc_imaging_lab
    ON m.npi = asc_imaging_lab.npi

What explains provider gaps?​

provider_typecostpercentage
Ambulance3.61693e+090.0327203
Durable Medical Equipment & Medical Supplies1.81883e+090.0164539
Hospital Affiliated Entity1.07948e+090.00976547
Clinic/Center9.8897e+080.00894664
Non-emergency Medical Transport (VAN)9.52353e+080.00861539
SQL
WITH counts AS (
    SELECT npi, sum(total_cost) as cost
    FROM tq_production.employernc_results.main_qc
    WHERE npi IS NOT NULL
    GROUP BY npi
)
SELECT pd.provider_type, SUM(cost) as cost
FROM counts m
LEFT JOIN (
    SELECT DISTINCT npi FROM tq_production.spines.spines_provider
) p 
    ON m.npi = p.npi
LEFT JOIN (
    SELECT DISTINCT npi FROM tq_production.spines.spines_provider_hospitals_additional_npis
) spa
    ON m.npi = spa.npi
LEFT JOIN (
    SELECT DISTINCT npi FROM tq_production.definitive_healthcare.physician_groups_physicians
) def
    ON m.npi = CAST(def.npi AS VARCHAR)
LEFT JOIN (
    SELECT DISTINCT npi_number as npi FROM tq_production.definitive_healthcare.physician_groups_associated_npi_numbers
) def_assoc
    ON m.npi = CAST(def_assoc.npi AS VARCHAR)
LEFT JOIN (
    SELECT DISTINCT individual_npi as npi FROM tq_dev.internal_dev_csong_graph.pecos_data_clean_provider_types_2025_07
) pecos
    ON m.npi = pecos.npi
LEFT JOIN (
    SELECT DISTINCT npi
    FROM tq_production.spines.spines_provider_ascs
    UNION 
    SELECT DISTINCT npi
    FROM tq_production.spines.spines_provider_imaging_centers
    UNION
    SELECT DISTINCT npi
    FROM tq_production.spines.spines_provider_ascs_additional_npis
    UNION 
    SELECT DISTINCT t.npi
    FROM tq_dev.internal_dev_csong_cld_v2_1_2.tmp_ref_provider_spine_2025_07, unnest(npi) as t(npi)
    WHERE provider_type LIKE '%Lab%'
) asc_imaging_lab
    ON m.npi = asc_imaging_lab.npi
LEFT JOIN (
    SELECT npi, ANY_VALUE(provider_type) as provider_type
    FROM tq_production.reference_legacy.provider_demographics
    GROUP BY 1
) pd
    ON m.npi = pd.npi
WHERE
    p.npi IS NULL 
    AND def.npi IS NULL 
    AND spa.npi IS NULL
    AND def_assoc.npi IS NULL
    AND pecos.npi IS NULL
    AND asc_imaging_lab.npi IS NULL
GROUP BY 1
ORDER BY 2 DESC

Codes​

in_clear_ratescostpercentage
In Clear Rates1.38432e+110.847651
Not in Clear Rates2.48806e+100.152349
SQL
WITH 
counts AS (
    SELECT billing_code, sum(total_cost) as cost
    FROM tq_production.employernc_results.main_qc
    GROUP BY billing_code
)
SELECT 
    CASE
        WHEN cr.billing_code IS NULL
        THEN 'Not in Clear Rates'
        ELSE 'In Clear Rates'
    END as in_clear_rates,
    ROUND(SUM(cost), 0) as cost    
FROM counts c
LEFT JOIN tq_dev.internal_dev_csong_cld_v2_1_2.prod_rollup_code cr
    ON c.billing_code = cr.billing_code
GROUP BY 1

What explains code gaps?​

tip

As of 09-20-2025, Clear Rates team is currently working on adding CAR-T and TRANSPLANTS DRGs.

billing_codeservice_descriptionutilizationcostpercentage
G0378HOSPITAL OBSERVATION SERVICE, PER HOUR2.91685e+063.95051e+090.172009
A0425GROUND MILEAGE, PER STATUTE MILE3.81857e+063.32255e+090.144667
A0380BLS MILEAGE (PER MILE)70234.71.80709e+090.0786822
018CHIMERIC ANTIGEN RECEPTOR (CAR) T-CELL AND OTHER IMMUNOTHERAPIES2074.241.2228e+090.0532419
A4649SURGICAL SUPPLY; MISCELLANEOUS1250567.5329e+080.0327989
019SIMULTANEOUS PANCREAS AND KIDNEY TRANSPLANT WITH HEMODIALYSIS4362.776.22245e+080.0270931
B9998NOC FOR ENTERAL SUPPLIES84653.35.61455e+080.0244462
A9270NON-COVERED ITEM OR SERVICE6894215.41584e+080.023581
017AUTOLOGOUS BONE MARROW TRANSPLANT WITHOUT CC/MCC4381.824.45821e+080.0194114
005LIVER TRANSPLANT WITH MCC OR INTESTINAL TRANSPLANT2073.974.27787e+080.0186263
003ECMO OR TRACHEOSTOMY WITH MV >96 HOURS OR PRINCIPAL DIAGNOSIS EXCEPT FACE, MOUTH AND NECK WITH MAJOR O.R. PROCEDURES939.743.6386e+080.0158428
002HEART TRANSPLANT OR IMPLANT OF HEART ASSIST SYSTEM WITHOUT MCC1662.273.55453e+080.0154768
A0427AMBULANCE SERVICE, ADVANCED LIFE SUPPORT, EMERGENCY TRANSPORT, LEVEL 1 (ALS 1 - EMERGENCY)1857303.12725e+080.0136163
007LUNG TRANSPLANT1207.623.0759e+080.0133927
006LIVER TRANSPLANT WITHOUT MCC3394.413.02936e+080.0131901
A5120SKIN BARRIER, WIPES OR SWABS, EACH1.11982e+062.71319e+080.0118135
C9399UNCLASSIFIED DRUGS OR BIOLOGICALS9344.082.67604e+080.0116517
A4456ADHESIVE REMOVER, WIPES, ANY TYPE, EACH1.03959e+062.57324e+080.0112041
004TRACHEOSTOMY WITH MV >96 HOURS OR PRINCIPAL DIAGNOSIS EXCEPT FACE, MOUTH AND NECK WITHOUT MAJOR O.R. PROCEDURES964.062.51031e+080.0109301
A4335INCONTINENCE SUPPLY; MISCELLANEOUS3.04075e+062.27153e+080.00989045
014ALLOGENEIC BONE MARROW TRANSPLANT9142.13779e+080.00930813
S5000PRESCRIPTION DRUG, GENERIC3833002.12932e+080.00927125
A0429AMBULANCE SERVICE, BASIC LIFE SUPPORT, EMERGENCY TRANSPORT (BLS-EMERGENCY)1581272.06331e+080.00898386
010PANCREAS TRANSPLANT1300.471.79185e+080.0078019
001HEART TRANSPLANT OR IMPLANT OF HEART ASSIST SYSTEM WITH MCC347.811.47104e+080.00640505
A0999UNLISTED AMBULANCE SERVICE1418.251.45427e+080.00633202
016AUTOLOGOUS BONE MARROW TRANSPLANT WITH CC/MCC1275.551.41649e+080.00616753
A0428AMBULANCE SERVICE, BASIC LIFE SUPPORT, NON-EMERGENCY TRANSPORT, (BLS)91786.11.24728e+080.00543075
A4554DISPOSABLE UNDERPADS, ALL SIZES1.00866e+071.22994e+080.00535527
T1015CLINIC VISIT/ENCOUNTER, ALL-INCLUSIVE5189301.19103e+080.00518586
E0601CONTINUOUS POSITIVE AIRWAY PRESSURE (CPAP) DEVICE1.02219e+061.12599e+080.00490266
96376THERAPEUTIC, PROPHYLACTIC, OR DIAGNOSTIC INJECTION (SPECIFY SUBSTANCE OR DRUG); EACH ADDITIONAL SEQUENTIAL INTRAVENOUS PUSH OF THE SAME SUBSTANCE/DRUG PROVIDED IN A FACILITY (LIST SEPARATELY IN ADDITION TO CODE FOR PRIMARY PROCEDURE)1433531.06593e+080.00464116
L8699PROSTHETIC IMPLANT, NOT OTHERWISE SPECIFIED5924.019.23925e+070.00402285
S9083GLOBAL FEE URGENT CARE CENTERS7351758.12349e+070.00353704
008SIMULTANEOUS PANCREAS AND KIDNEY TRANSPLANT810.727.75983e+070.0033787
0241UINFECTIOUS DISEASE (VIRAL RESPIRATORY TRACT INFECTION), PATHOGEN-SPECIFIC RNA, 4 TARGETS (SEVERE ACUTE RESPIRATORY SYNDROME CORONAVIRUS 2 [SARS-COV-2], INFLUENZA A, INFLUENZA B, RESPIRATORY SYNCYTIAL VIRUS [RSV]), UPPER RESPIRATORY SPECIMEN, EACH PATHOGEN REPORTED AS DETECTED OR NOT DETECTED2806197.52019e+070.00327436
H0046MENTAL HEALTH SERVICES, NOT OTHERWISE SPECIFIED3828617.42695e+070.00323376
S5125ATTENDANT CARE SERVICES; PER 15 MINUTES1.39441e+077.42086e+070.00323111
E1390OXYGEN CONCENTRATOR, SINGLE DELIVERY PORT, CAPABLE OF DELIVERING 85 PERCENT OR GREATER OXYGEN CONCENTRATION AT THE PRESCRIBED FLOW RATE7465967.22156e+070.00314433
A0390ALS MILEAGE (PER MILE)2075307.18775e+070.00312961
T4535DISPOSABLE LINER/SHIELD/GUARD/PAD/UNDERGARMENT, FOR INCONTINENCE, EACH7.82326e+066.72705e+070.00292902
T5999SUPPLY, NOT OTHERWISE SPECIFIED5409.96.1336e+070.00267063
V2520CONTACT LENS, HYDROPHILIC, SPHERICAL, PER LENS1144475.8076e+070.00252868
T4525ADULT SIZED DISPOSABLE INCONTINENCE PRODUCT, PROTECTIVE UNDERWEAR/PULL-ON, SMALL SIZE, EACH6611115.74322e+070.00250065
H0020ALCOHOL AND/OR DRUG SERVICES; METHADONE ADMINISTRATION AND/OR SERVICE (PROVISION OF THE DRUG BY A LICENSED PROGRAM)7901365.72076e+070.00249087
B9999NOC FOR PARENTERAL SUPPLIES482.085.15153e+070.00224302
013TRACHEOSTOMY FOR FACE, MOUTH AND NECK DIAGNOSES OR LARYNGECTOMY WITHOUT CC/MCC927.074.80269e+070.00209113
T4522ADULT SIZED DISPOSABLE INCONTINENCE PRODUCT, BRIEF/DIAPER, MEDIUM, EACH3.01639e+064.75897e+070.0020721
E2402NEGATIVE PRESSURE WOUND THERAPY ELECTRICAL PUMP, STATIONARY OR PORTABLE91156.14.22957e+070.00184159
A0431AMBULANCE SERVICE, CONVENTIONAL AIR SERVICES, TRANSPORT, ONE WAY (ROTARY WING)28144.21195e+070.00183392
SQL
WITH 
counts AS (
    SELECT billing_code, sum(total_cost) as cost, sum(utilization) as utilization
    FROM tq_production.employernc_results.main_qc
    GROUP BY billing_code
)
SELECT 
    c.billing_code, 
    cs.service_description,
    c.utilization,
    c.cost
FROM counts c
LEFT JOIN tq_dev.internal_dev_csong_cld_v2_1_2.prod_rollup_code cr
    ON c.billing_code = cr.billing_code
LEFT JOIN tq_production.spines.spines_services cs
    ON c.billing_code = cs.service_code
WHERE cr.billing_code IS NULL
AND cs.service_type IN ('HCPCS', 'CPT', 'MS-DRG')
ORDER BY c.cost DESC
LIMIT 1000
On this page: