5.3 Clinical & Financial Data Extraction

Clinical & Financial Data Extraction

Data extraction represents the bridge between raw healthcare databases and actionable analytical intelligence. For a Certified Health Data Analyst (CHDA), writing precise, performant Structured Query Language (SQL) queries and parsing complex electronic healthcare transaction sets (such as ANSI ASC X12N 837 claims) are essential competencies. Healthcare data extraction requires not only technical proficiency with relational algebra, Common Table Expressions (CTEs), and window functions, but also deep domain expertise in clinical event sequencing, CMS quality program logic, and reimbursement claim structures.

---

1. SQL Fundamentals & Query Execution Order for Health Data Analysts

To write bug-free queries, a health data analyst must understand the discrepancy between the syntactic order (how SQL is written) and the logical execution order (how the database engine processes the query):

+---------------------------------------------------------------------------------------------------+
|                         LOGICAL SQL QUERY PROCESSING PIPELINE                                     |
+---------------------------------------------------------------------------------------------------+
  [1. FROM & JOIN]      --> Identify source tables, evaluate Cartesian products & join conditions
  [2. WHERE]            --> Filter individual base rows before grouping (Pre-aggregation filtering)
  [3. GROUP BY]         --> Collapse remaining rows into summary groups based on grouping keys
  [4. HAVING]           --> Filter summary groups based on aggregate criteria (Post-aggregation)
  [5. SELECT]           --> Evaluate expressions, column projections, and window functions
  [6. DISTINCT]         --> Eliminate duplicate projected rows
  [7. ORDER BY]         --> Sort the final result dataset (Ascending / Descending)
  [8. LIMIT / TOP]      --> Restrict the number of output rows returned to the client
+---------------------------------------------------------------------------------------------------+

Critical Healthcare Filtering Distinction: WHERE vs. HAVING

• WHERE Clause (Pre-Aggregation): Filters individual row-level clinical observations before any grouping occurs. For example, filtering for laboratory records where lab_result_value > 140 removes non-qualifying lab results before calculating averages.
• HAVING Clause (Post-Aggregation): Filters summarized group records after the GROUP BY operation has aggregated data. For example, identifying super-utilizer patients who have had at least 4 distinct inpatient admissions:

SELECT 
    patient_id,
    COUNT(DISTINCT encounter_id) AS total_admissions,
    AVG(length_of_stay_days) AS average_los
FROM fact_inpatient_encounters
WHERE discharge_date >= '2025-01-01' -- Pre-filter: Only 2025 discharges
GROUP BY patient_id
HAVING COUNT(DISTINCT encounter_id) >= 4; -- Post-filter: Only patients with >= 4 stays

Common Analytical Pitfall: Placing an aggregate condition in the WHERE clause (e.g., WHERE COUNT(encounter_id) >= 4) triggers a database syntax error because individual rows cannot evaluate aggregate summaries.

---

2. Relational Joins in Healthcare Analytics Contexts

Relational joins merge data across normalized or dimensional tables based on common relational keys. Selecting the incorrect join type fundamentally alters clinical cohort counts and financial totals.

+---------------------------------------------------------------------------------------------------+
|                                 RELATIONAL JOINS IN HEALTHCARE                                    |
+-----------------------------------+-----------------------------------+---------------------------+
| INNER JOIN                        | LEFT OUTER JOIN                   | FULL OUTER JOIN           |
| - Only matching records in BOTH   | - ALL records from left table     | - ALL records from BOTH   |
| - Encounters WITH confirmed DX    | - Left: All admitted patients     | - Left: Hospital billing  |
| - Drops unmapped records          | - Right: Readmission events       | - Right: Payer remit 835  |
|                                   | - Non-readmits return NULL        | - Highlights unbilled/unpd|
+-----------------------------------+-----------------------------------+---------------------------+

1. INNER JOIN

Returns only rows where the join predicate matches in both tables. Records without a corresponding match in either table are completely excluded.

• Healthcare Context: Extracting all inpatient encounters that have a confirmed, finalized primary diagnosis coded in the diagnosis table:

SELECT 
    e.encounter_id,
    e.patient_id,
    e.admit_date,
    d.icd10_code,
    d.diagnosis_description
FROM fact_encounters e
INNER JOIN dim_diagnosis d 
    ON e.primary_diagnosis_sk = d.diagnosis_sk;

2. LEFT OUTER JOIN

Returns all rows from the left table, along with matched rows from the right table. When no match exists in the right table, all right-table columns return NULL.

• Healthcare Context: Extracting a complete cohort of heart failure patients and determining whether each patient experienced a subsequent hospital readmission. Patients who were not readmitted are retained in the output dataset with NULL readmission attributes:

SELECT 
    index_stay.patient_id,
    index_stay.encounter_id AS index_encounter_id,
    index_stay.discharge_date AS index_discharge_date,
    readmit_stay.encounter_id AS readmit_encounter_id,
    readmit_stay.admit_date AS readmit_admit_date,
    CASE 
        WHEN readmit_stay.encounter_id IS NOT NULL THEN 1 
        ELSE 0 
    END AS readmission_flag
FROM fact_inpatient_encounters index_stay
LEFT JOIN fact_inpatient_encounters readmit_stay 
    ON index_stay.patient_id = readmit_stay.patient_id
    AND readmit_stay.admit_date > index_stay.discharge_date
    AND readmit_stay.admit_date <= index_stay.discharge_date + INTERVAL '30 days'
WHERE index_stay.primary_diagnosis_code LIKE 'I50%'; -- Heart Failure

3. FULL OUTER JOIN

Returns all rows from both tables, pairing matching rows where available and filling NULL values for unmatched rows on either side.

• Healthcare Context: Reconciling internal hospital billing ledger claims against external payer Electronic Remittance Advice (EDI 835) files. Highlights claims billed by the hospital with no payer remittance, as well as remittances received with no matching internal claim record.

4. CROSS JOIN (Cartesian Product)

Pairs every row of the left table with every row of the right table ($N \times M$ rows). Used in healthcare analytics to generate dense date grids crossed with hospital nursing units to ensure zero-filling for daily bed occupancy reporting on days with zero admissions.

---

3. Subqueries, Common Table Expressions (CTEs) & Window Functions

Complex clinical analytics require multi-stage data pipelines and non-aggregating partition calculations.

Subqueries vs. Common Table Expressions (CTEs)

• Subqueries: Queries nested inside an outer SELECT, FROM, or WHERE clause. While effective for simple lookups, deeply nested subqueries become unreadable and difficult to debug.
• Common Table Expressions (CTEs - WITH clauses): Define named, temporary result sets that can be referenced sequentially within the main query. CTEs dramatically improve code readability, maintainability, and modularity when constructing multi-stage clinical cohorts.

Advanced Window Functions in Healthcare

Unlike standard aggregate functions (SUM, AVG) which collapse multiple rows into a single summary output, Window Functions perform calculations across a specific subset (window) of rows related to the current row while preserving every individual row's granularity.

FUNCTION() OVER (
    PARTITION BY partition_column -- Defines the cohort grouping (e.g., patient_id)
    ORDER BY sort_column          -- Defines the sequence within the window (e.g., admit_date)
    ROWS/RANGE frame_clause       -- Defines the physical sliding window boundaries
)

1. ROW_NUMBER(), RANK(), and DENSE_RANK()

• ROW_NUMBER(): Assigns a unique sequential integer (1, 2, 3...) to each row within a partition. Ideal for selecting the single most recent encounter or the primary attending physician:

-- Extract only the most recent HbA1c test result for each diabetic patient
WITH ranked_labs AS (
    SELECT 
        patient_id,
        lab_order_id,
        lab_result_date,
        lab_result_value,
        ROW_NUMBER() OVER (
            PARTITION BY patient_id 
            ORDER BY lab_result_date DESC
        ) AS rn
    FROM fact_lab_results
    WHERE loinc_code IN ('4548-4', '17856-6') -- HbA1c LOINC codes
)
SELECT patient_id, lab_order_id, lab_result_date, lab_result_value
FROM ranked_labs
WHERE rn = 1; -- Filter for most recent test

• RANK() vs. DENSE_RANK(): Used when ranking providers by clinical volume or quality scores. When ties occur, RANK() skips subsequent ranks (e.g., 1, 2, 2, 4), whereas DENSE_RANK() does not skip ranks (e.g., 1, 2, 2, 3).

2. LAG() and LEAD() Functions

Access values from preceding (LAG) or subsequent (LEAD) rows within a partition without executing expensive self-joins.

• LAG(column, offset): Looks backward to retrieve attributes from a prior encounter (e.g., retrieving previous discharge date to calculate days since last discharge).
• LEAD(column, offset): Looks forward to retrieve attributes from a future encounter (e.g., retrieving next admission date to evaluate readmission timing).

---

4. Healthcare-Specific SQL Logic & Worked Queries

1. Inpatient Length of Stay (LOS) Calculation

Under standard hospital billing rules, Length of Stay (LOS) represents the number of days a patient occupied an inpatient bed. The admission day is counted, but the discharge day is not. However, if a patient is admitted and discharged on the same calendar day, the stay is statistically credited as 1 day (never 0 days):

SELECT 
    encounter_id,
    patient_id,
    admit_date,
    discharge_date,
    CASE 
        WHEN DATEDIFF(day, admit_date, discharge_date) = 0 THEN 1
        ELSE DATEDIFF(day, admit_date, discharge_date)
    END AS calculated_inpatient_los_days
FROM fact_inpatient_encounters;

2. CMS 30-Day All-Cause Readmission Identification Query

The CMS Hospital Readmissions Reduction Program (HRRP) evaluates unplanned 30-day all-cause readmissions following an eligible index admission. Stays where the patient expired in the hospital (discharge_disposition = '20') or left Against Medical Advice (AMA) (discharge_disposition = '07') are statutory exclusions from index admission cohorts:

WITH encounter_trajectory AS (
    SELECT 
        patient_id,
        encounter_id AS index_encounter_id,
        admit_date AS index_admit_date,
        discharge_date AS index_discharge_date,
        discharge_disposition,
        primary_diagnosis_code,
        -- Retrieve the next subsequent admission date for this patient
        LEAD(encounter_id) OVER (
            PARTITION BY patient_id 
            ORDER BY admit_date
        ) AS next_encounter_id,
        LEAD(admit_date) OVER (
            PARTITION BY patient_id 
            ORDER BY admit_date
        ) AS next_admit_date
    FROM fact_inpatient_encounters
    WHERE encounter_type = 'Inpatient'
)
SELECT 
    index_encounter_id,
    patient_id,
    index_admit_date,
    index_discharge_date,
    primary_diagnosis_code,
    next_encounter_id AS readmit_encounter_id,
    next_admit_date AS readmit_admit_date,
    DATEDIFF(day, index_discharge_date, next_admit_date) AS days_to_readmit,
    CASE 
        WHEN DATEDIFF(day, index_discharge_date, next_admit_date) BETWEEN 1 AND 30 
        THEN 1 
        ELSE 0 
    END AS is_30day_readmission
FROM encounter_trajectory
WHERE discharge_disposition NOT IN ('20', '07') -- Exclude in-hospital mortality & AMA
  AND index_discharge_date <= CURRENT_DATE - INTERVAL '30 days'; -- Ensure full 30-day follow-up

3. Accurate Patient Age Calculation at Admission

Calculating patient age requires precise handling of leap years and whether the patient's birthday has occurred prior to the admission date in the encounter year:

SELECT 
    patient_id,
    date_of_birth,
    admit_date,
    DATEDIFF(year, date_of_birth, admit_date) - 
    CASE 
        WHEN (MONTH(date_of_birth) > MONTH(admit_date)) 
          OR (MONTH(date_of_birth) = MONTH(admit_date) AND DAY(date_of_birth) > DAY(admit_date)) 
        THEN 1 
        ELSE 0 
    END AS exact_age_at_admission
FROM dim_patient p
INNER JOIN fact_encounters e ON p.patient_sk = e.patient_sk;

4. Defensive SQL & NULL Handling

Healthcare datasets frequently contain missing or null values in optional fields. Robust queries use COALESCE() and NULLIF() to prevent runtime errors and data skew:

• COALESCE(expression, default_value): Returns the first non-null argument in the list. Useful for populating default insurance categories: COALESCE(primary_payer_name, 'Self-Pay / Uninsured').
• NULLIF(value1, value2): Returns NULL if value1 = value2. Essential for preventing catastrophic divide-by-zero errors when calculating hospital Cost-to-Charge Ratios (CCR) or unit costs:

SELECT 
    department_id,
    total_costs,
    total_charges,
    -- Prevent divide-by-zero if total_charges is 0
    total_costs / NULLIF(total_charges, 0) AS calculated_ccr
FROM fact_department_financials;

---

5. Extracting & Parsing Electronic Claims Feeds (EDI)

Healthcare billing and claims feeds are transmitted using standardized electronic data interchange (EDI) formats governed by the ANSI ASC X12N standards mandated under HIPAA.

+---------------------------------------------------------------------------------------------------+
|                         ANSI ASC X12N 837 CLAIMS DATA STRUCTURE                                  |
+---------------------------------------------------------------------------------------------------+
  LOOP 1000A / 1000B : Submitter & Receiver Information (Clearinghouse / Payer Identifiers)
  LOOP 2000A / 2010AA: Billing Provider Hierarchical Level & Identity (NPI, Tax ID, Address)
  LOOP 2000B / 2010BA: Subscriber & Patient Demographics (Member ID, Group Number, DOB, Gender)
  LOOP 2300          : Claim Information Header (Total Charges, DRG, Principal Diagnosis, Dates)
  LOOP 2400          : Service Line Detail (Revenue Codes [SV2], CPT/HCPCS [SV1], Modifiers, Units)
+---------------------------------------------------------------------------------------------------+

837I (Institutional) vs. 837P (Professional) Claims

• ANSI ASC X12N 837I (Institutional Claim): Electronic equivalent of the UB-04 / CMS-1450 paper claim. Used by hospitals, skilled nursing facilities, home health agencies, and hospices. Captures facility revenue codes, ICD-10-PCS inpatient procedure codes, and MS-DRG/APC groupers.
• ANSI ASC X12N 837P (Professional Claim): Electronic equivalent of the CMS-1500 paper claim. Used by physicians, mid-level practitioners, and freestanding outpatient clinics. Captures CPT/HCPCS procedure codes, professional modifiers, and Place of Service (POS) codes.

EDI File Structure & Delimiters

EDI transaction sets are plain-text, delimited data streams structured into segments and data elements:

• Segment Terminator (typically ~ or newline): Marks the conclusion of a segment line (e.g., CLM...~).
• Element Separator (typically *): Separates individual data elements within a segment (e.g., HI*ABK:I509~).
• Sub-element / Component Separator (typically :): Separates sub-components within a composite data element (e.g., qualifier ABK and ICD-10 code I509).

Sample 837 Claims Stream Breakdown

CLM*CLAIM90210*14500.00***11:A:1*Y*A*Y*Y~
DTP*434*RD8*20260301-20260305~
CL1*1*1*01~
HI*ABK:I509*ABF:I10*ABF:E119~
LX*1~
SV2*0450*HC:99284*750.00*UN*1~
LX*2~
SV2*0250*HC:J0696:25*450.00*UN*2~

Parsing Key Financial Elements

1. Revenue Codes (837I Loop 2400 SV2 Segment): Standardized 4-digit codes identifying the specific hospital accommodation or ancillary department furnishing the service:
   • 0450 – Emergency Room
   • 0250 – Pharmacy (General)
   • 0200 – Intensive Care Unit (ICU)
   • 0300 – Laboratory (General)
   • 0320 – Radiology (Diagnostic)
   • 0360 – Operating Room Services
2. CPT/HCPCS Procedure Modifiers: Two-character alphanumeric codes appended to procedure codes in SV1 or SV2 to give vital clinical context without changing the core definition of the code:
   • Modifier -25: Significant, separately identifiable Evaluation and Management (E/M) service by the same physician on the same day of a minor procedure.
   • Modifier -59: Distinct procedural service performed on the same day, breaking standard NCCI unbundling edits.
   • Modifier -X{EPSU}: Specific subsets of modifier -59 (-XE Separate Encounter, -XP Separate Practitioner, -XS Separate Structure/Organ, -XU Unusual Non-Overlapping Service).
   • Modifier -TC: Technical Component only (facility overhead, equipment, technologist time).
   • Modifier -26: Professional Component only (physician interpretation and report).