Skip to main content

Multiple Anchor Procedures on a Facility Claim

Date: 2026-04-20

Source tables (all in tq_dev.internal_dev_csong_ssp):

  • medical_headers_both
  • medical_service_lines_both
  • allowed_amounts_both
  • ssp_groupings_v3 — anchor codes per SSP

Question​

For three sample SSPs — bronchoscopy, colonoscopy, and upper endoscopy (EGD) — how many facility claims carry more than one anchor-code procedure, and for each such anchor, is there a matching row in allowed_amounts_both?

Scope & filters​

  • Facility OP claims only: medical_headers_both.claim_pos = 'OP' and bill_type_code LIKE '13%'.
  • Service-line year >= 2024 (consistent with the SSP pricing pipeline).
  • A "multiple anchor" claim is one encounter_key with >=2 distinct anchor CPTs on its service lines (medical_service_lines_both.procedure_code).
  • Anchor codes come from ssp_groupings_v3 for:
    • PU.0.bronchoscopy — 14 CPTs: 31622, 31623, 31624, 31625, 31628, 31629, 31635, 31640, 31641, 31645, 31646, 31652, 31653, 31660
    • GA.0.colonoscopy — 21 CPTs: 44388–44404, 45378–45392, G0105, G0121
    • GA.0.egd — 24 CPTs: 43235–43270

Allowed-amount join​

allowed_amounts_both has no revenue_code column — its procedure_code can hold either a CPT/HCPCS or a 4-digit revenue code (57.9M of the 4.5B 2024+ rows are 4-digit numeric). So for each (encounter, anchor_code, revenue_code) service line we join twice via a single OR:

LEFT JOIN allowed_amounts_both aa
  ON aa.encounter_key = sl.encounter_key
 AND aa.year >= 2024
 AND (aa.procedure_code = sl.anchor_code         -- CPT match
      OR aa.procedure_code = sl.revenue_code)   -- rev-code match

Per-line flag:

FlagMeaning
has_allowed_by_cptthere's an allowed_amounts_both row on that encounter whose procedure_code equals the CPT anchor

The OR branch on revenue_code is kept in the join for completeness (the SSP pipeline's semantics allow rev-code-level allowed amounts), but it fires 0× for our three SSPs — see Key findings — so no per-line by_rc flag is reported.

Headline numbers (2024+, OP facility claims only)​

Multi-anchor encounter counts​

SSPTotal facility encs with ≥1 anchorMulti-anchor encs (≥2 distinct)% multi3+ anchors
GA.0.colonoscopy3,765,200608,07916.2%56,298
GA.0.egd2,461,986326,97313.3%23,437
PU.0.bronchoscopy228,427126,94655.6%73,284

Bronchoscopy claims bundle multiple anchors more than half the time. Many bronchoscopy anchor CPTs are "add-on" style (e.g. 31625 biopsy, 31628 transbronchial biopsy, 31645/31646 therapeutic aspiration) billed alongside the diagnostic base (31622/31623/31624).

Allowed-amount coverage across multi-anchor encs​

Computed over every (encounter_key, anchor_code) line in multi-anchor encounters.

SSPAnchor linesCPT matchAny matchEncs: all anchors allowedEncs: none allowedEncs: partial
GA.0.colonoscopy1,325,86133.2%33.2%170,969 (28%)376,505 (62%)60,605 (10%)
GA.0.egd695,25628.6%28.6%59,494 (18%)196,436 (60%)71,043 (22%)
PU.0.bronchoscopy389,86024.2%24.2%18,507 (15%)74,474 (59%)33,965 (27%)

Column definitions​

  • Anchor lines — count of (encounter_key, anchor_code) pairs across every multi-anchor encounter for the SSP. An encounter with 3 distinct anchors contributes 3 anchor lines.
  • CPT match — share of anchor lines where allowed_amounts_both has a row on the same encounter with procedure_code = anchor_code.
  • Any match — share of anchor lines matched by the full OR join (CPT or revenue code). Equal to CPT match here because the rev-code branch fires 0× in this slice; kept as a separate column so the metric generalises to other SSPs where rev-code-level allowed amounts do surface.
  • Encs: all anchors allowed — encounters where every anchor line has an "Any match" hit. These are cases where the allowed-amount table attributes a payment to each distinct anchor CPT on the claim.
  • Encs: none allowed — encounters where no anchor line gets any match. The encounter itself may still have allowed-amount rows (e.g. on non-anchor procedure codes or revenue codes), but none tied to the anchors we're pricing.
  • Encs: partial — encounters where some anchors match and others don't — the ambiguous middle where an SSP-level allowed amount can't be cleanly assembled from the per-anchor rows.

Key findings​

  1. Only ~25–33% of anchor-code lines on a multi-anchor encounter have a matching allowed_amounts_both row by CPT. Even when a claim carries multiple anchors, most anchors don't surface an allowed amount.
  2. The revenue-code branch of the union fires 0× in this slice. allowed_amounts_both does contain 4-digit numeric codes overall (see "Allowed-amount join" above), but for this set of facility claims the (encounter_key, revenue_code) pairs don't match.
  3. Partial coverage is common — 10–27% of multi-anchor encs have an allowed amount for some anchors but not others. That's the population where the joined allowed amount can't be cleanly attributed to the encounter as a whole.
  4. The majority (59–62%) of multi-anchor encs have no allowed amount on any of the anchor-code lines.

Examples​

150 distinct encounters (50 per SSP, 383 rows total) are available as a downloadable TSV:

Download samples.tsv

Columns: ssp_grouper, visit_id, encounter_key, bill_type_code, hco_1_npi, claim_date, anchor_code, revenue_code, line_charge, units, has_allowed_by_cpt, allowed_by_cpt.

Colonoscopy — example encounters​

encounter_key (prefix)claim_dateanchorrevline_chargeby_cpt?allowed_by_cpt
00000c5e…2024-04-054438907504,119.001676.68
00000c5e…2024-04-054439407503,780.000—
00002b09…2025-03-31453800750—0—
00002b09…2025-03-31453850750—0—
00003f20…2024-05-104538007502,970.000—
00003f20…2024-05-104538507503,780.000—
00003f20…2024-05-104538807503,555.000—
000055e8…2024-10-074538007501,614.0011,614.00
000055e8…2024-10-074538507501,614.0011,614.00
0000bb05…2024-01-244538007502,127.0012,127.00
0000bb05…2024-01-244538507502,127.0012,127.00

First encounter 00000c5e… is a clean case: 44389 (with biopsy) gets an allowed amount but the add-on 44394 does not. 000055e8… and 0000bb05… are cases where every anchor line has its own allowed amount.

EGD — example encounters​

encounter_key (prefix)claim_dateanchorrevline_chargeby_cpt?allowed_by_cpt
000008f8…2024-10-074323707502,357.680—
000008f8…2024-10-074323907502,357.660—
00008e6a…2025-08-1143239——0—
00008e6a…2025-08-1143249——0—
00013d9d…2025-03-104323907509,540.00143.05
00013d9d…2025-03-1043244075012,228.00143.05

Bronchoscopy — example encounters​

encounter_key (prefix)claim_dateanchorrevline_chargeby_cpt?allowed_by_cpt
0000011e…2024-03-043162407502,973.2511,486.62
0000011e…2024-03-043164507500.000—
00001695…2024-12-0531624——0—
00001695…2024-12-0531628——0—
00001695…2024-12-0531629——16,489.71
000056c2…2024-12-103162903616,285.4813,771.29
000056c2…2024-12-1031653075025,849.68115,509.81

Pattern across all three SSPs: base-diagnostic codes (31622/31623/31624, 43235/43239, 45378/45380) more often surface an allowed amount than therapeutic add-ons (31625/31628/31645, 43247/43255, 44394).

Reproducibility​

The query first materialises a helper table of all (encounter, anchor) line-level rows, then samples 50 multi-anchor encounters per SSP and attaches allowed-amount flags.

-- Step 1: materialise facility anchor lines
CREATE TABLE tq_dev.internal_dev_csong_ssp.tmp_mp_fal AS
WITH anchors AS (
    SELECT DISTINCT ssp_grouper, code AS anchor_code
    FROM tq_dev.internal_dev_csong_ssp.ssp_groupings_v3
    WHERE ssp_grouper IN ('PU.0.bronchoscopy','GA.0.colonoscopy','GA.0.egd')
      AND type = 'CPT'
)
SELECT DISTINCT
    a.ssp_grouper,
    mh.visit_id,
    msl.encounter_key,
    mh.bill_type_code,
    mh.hco_1_npi,
    mh.claim_date,
    msl.procedure_code AS anchor_code,
    msl.revenue_code,
    msl.line_charge,
    msl.units
FROM tq_dev.internal_dev_csong_ssp.medical_headers_both mh
JOIN tq_dev.internal_dev_csong_ssp.medical_service_lines_both msl
    ON mh.encounter_key = msl.encounter_key
    AND msl.year >= 2024
JOIN anchors a ON msl.procedure_code = a.anchor_code
WHERE mh.claim_pos = 'OP'
  AND mh.year >= 2024
  AND mh.bill_type_code LIKE '13%';

-- Step 2: sample 50 multi-anchor encs per SSP and attach allowed flags
WITH multi_anchor_encs AS (
    SELECT ssp_grouper, encounter_key
    FROM tq_dev.internal_dev_csong_ssp.tmp_mp_fal
    GROUP BY 1, 2
    HAVING COUNT(DISTINCT anchor_code) >= 2
),
ranked_encs AS (
    SELECT ssp_grouper, encounter_key,
           ROW_NUMBER() OVER (PARTITION BY ssp_grouper ORDER BY encounter_key) AS rn
    FROM multi_anchor_encs
),
sampled_encs AS (
    SELECT ssp_grouper, encounter_key FROM ranked_encs WHERE rn <= 50
),
sampled_lines AS (
    SELECT fal.*
    FROM tq_dev.internal_dev_csong_ssp.tmp_mp_fal fal
    JOIN sampled_encs s
      ON fal.ssp_grouper = s.ssp_grouper
     AND fal.encounter_key = s.encounter_key
)
SELECT
    sl.ssp_grouper, sl.visit_id, sl.encounter_key, sl.bill_type_code,
    sl.hco_1_npi, sl.claim_date, sl.anchor_code, sl.revenue_code,
    sl.line_charge, sl.units,
    MAX(CASE WHEN aa.procedure_code = sl.anchor_code THEN 1 ELSE 0 END) AS has_allowed_by_cpt,
    MAX(CASE WHEN aa.procedure_code = sl.anchor_code THEN aa.allowed_amount END) AS allowed_by_cpt
FROM sampled_lines sl
LEFT JOIN tq_dev.internal_dev_csong_ssp.allowed_amounts_both aa
    ON sl.encounter_key = aa.encounter_key
    AND aa.year >= 2024
    AND (aa.procedure_code = sl.anchor_code OR aa.procedure_code = sl.revenue_code)
GROUP BY 1,2,3,4,5,6,7,8,9,10
ORDER BY ssp_grouper, encounter_key, anchor_code;

DROP TABLE tq_dev.internal_dev_csong_ssp.tmp_mp_fal;

Aggregate allowed-amount coverage (the numbers in the second headline table)​

WITH multi_anchor_encs AS (
    SELECT ssp_grouper, encounter_key
    FROM tq_dev.internal_dev_csong_ssp.tmp_mp_fal
    GROUP BY 1, 2
    HAVING COUNT(DISTINCT anchor_code) >= 2
),
per_anchor AS (
    SELECT
        fal.ssp_grouper, fal.encounter_key, fal.anchor_code, fal.revenue_code,
        MAX(CASE WHEN aa.procedure_code = fal.anchor_code  THEN 1 ELSE 0 END) AS has_cpt,
        MAX(CASE WHEN aa.procedure_code = fal.revenue_code THEN 1 ELSE 0 END) AS has_rc
    FROM tq_dev.internal_dev_csong_ssp.tmp_mp_fal fal
    JOIN multi_anchor_encs m
      ON fal.ssp_grouper = m.ssp_grouper AND fal.encounter_key = m.encounter_key
    LEFT JOIN tq_dev.internal_dev_csong_ssp.allowed_amounts_both aa
        ON fal.encounter_key = aa.encounter_key
        AND aa.year >= 2024
        AND (aa.procedure_code = fal.anchor_code OR aa.procedure_code = fal.revenue_code)
    GROUP BY 1, 2, 3, 4
),
per_enc AS (
    SELECT
        ssp_grouper, encounter_key,
        COUNT(*) AS n_anchors,
        SUM(has_cpt) AS n_cpt_matched,
        SUM(has_rc)  AS n_rc_matched,
        SUM(CASE WHEN has_cpt = 1 OR has_rc = 1 THEN 1 ELSE 0 END) AS n_any_matched
    FROM per_anchor
    GROUP BY 1, 2
)
SELECT
    ssp_grouper,
    COUNT(*)              AS n_multi_anchor_encs,
    SUM(n_anchors)        AS n_anchor_lines,
    ROUND(1.0 * SUM(n_cpt_matched) / SUM(n_anchors), 3) AS pct_anchors_with_cpt_match,
    ROUND(1.0 * SUM(n_rc_matched)  / SUM(n_anchors), 3) AS pct_anchors_with_rc_match,
    ROUND(1.0 * SUM(n_any_matched) / SUM(n_anchors), 3) AS pct_anchors_with_any_match,
    COUNT_IF(n_any_matched = n_anchors)                          AS encs_all_anchors_allowed,
    COUNT_IF(n_any_matched = 0)                                  AS encs_no_anchors_allowed,
    COUNT_IF(n_any_matched > 0 AND n_any_matched < n_anchors)    AS encs_partial_anchors_allowed
FROM per_enc
GROUP BY 1
ORDER BY 1;
On this page: