SQL Operations for Encounter List Management

Code Explanation of Encounter List Operations

Below is a detailed explanation of the provided SQL code for creating and populating an encounters list table.

Create Temporary Table #enc_list

CREATE TABLE #enc_list (
    enc_id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    enc_type VARCHAR(20) NOT NULL,
    date_of_service DATE NOT NULL,
    wip_conversion_date DATE NOT NULL,
    primary_payer VARCHAR(40) NULL,
    financial_class VARCHAR(100) NULL,
    billed_date DATE NULL,
    enc_nbr VARCHAR(20) NULL,
    clinician_name VARCHAR(200) NULL,
    patient_nbr VARCHAR(20) NULL,
    age INT NULL,
    Zone VARCHAR(100) NULL,
    Region VARCHAR(100) NULL,
    PCC_name VARCHAR(100) NULL
);

Key Points

• Temporary Table: The # prefix indicates this is a temporary table.
• Columns:
  • enc_id: UNIQUEIDENTIFIER, primary key.
  • enc_type: VARCHAR(20), not null.
  • date_of_service, wip_conversion_date: DATE, not null.
  • Various optional (nullable) fields for billing and demographic data.

Insert Initial Data into #enc_list

The code inserts data into the temporary table using a SELECT statement.

INSERT #enc_list (
    enc_id,
    enc_type,
    date_of_service,
    wip_conversion_date
)
SELECT 
    C.source_id AS enc_id,
    MAX(C.cpt4_code_id) AS enc_type,
    CAST(MIN(C.begin_date_of_service) AS DATE) AS date_of_service,
    CAST(MIN(C.closing_date) AS DATE) AS wip_conversion_date
FROM dbo.charges C
WHERE
    C.practice_id <> '0001' AND
    C.service_item_id IN ('L1907', 'L1940', 'L1960', 'L1970', 'L1950', 'L1945','L1932','L1951','L1950','L1945','L1932') AND
    ISNULL(C.closing_date, '') <> '' AND
    C.begin_date_of_service BETWEEN CONVERT(VARCHAR(8), @start_date, 112) AND CONVERT(VARCHAR(8), @end_date, 112)
GROUP BY C.source_id;

Key Points

• SELECT Statement: Gathers data from the dbo.charges table.
• Conditions:
  • Filters out specific practice_id.
  • Filters for designated service_item_id.
  • Ensures that closing_date is not NULL.
  • Filters date range using @start_date and @end_date.
• Aggregations: Uses MAX and MIN functions to handle date and code fetching per source_id.

Update Additional Fields in #enc_list

This UPDATE statement enriches #enc_list with linked data from several tables.

UPDATE EL
SET 
    EL.primary_payer = ISNULL(PM.payer_name, 'Self Pay'),
    EL.financial_class = ISNULL(FC.mstr_list_item_desc, 'Private Pay'),
    EL.enc_nbr = PE.practice_id + '_' + CAST(PE.enc_nbr AS VARCHAR(10)),
    EL.patient_nbr = P.person_nbr,
    EL.age = (0 + CONVERT(VARCHAR(8), GETDATE(), 112) - P.date_of_birth) / 10000,
    EL.clinician_name = RP.last_name + ', ' + RP.first_name,
    EL.Zone = ZML.mstr_list_item_desc,
    EL.Region = RML.mstr_list_item_desc,
    EL.PCC_name = LM.location_name
FROM #enc_list EL
INNER JOIN dbo.patient_encounter PE ON PE.enc_id = EL.enc_id
INNER JOIN dbo.person P ON P.person_id = PE.person_id
INNER JOIN dbo.location_mstr LM ON LM.location_id = PE.location_id
INNER JOIN dbo.provider_mstr RP ON RP.provider_id = PE.rendering_provider_id
LEFT JOIN dbo.mstr_lists RML ON RML.mstr_list_item_id = LM.location_subgrouping1_id
LEFT JOIN dbo.mstr_lists ZML ON ZML.mstr_list_item_id = LM.location_subgrouping2_id
LEFT JOIN dbo.payer_mstr PM ON PM.payer_id = PE.cob1_payer_id
LEFT JOIN dbo.mstr_lists FC ON PM.financial_class = FC.mstr_list_item_id;

Key Points

• Inner Joins: Link #enc_list with multiple tables (patient_encounter, person, location_mstr, provider_mstr) to fetch relevant data.
• Left Joins: Include optional data from mstr_lists and payer_mstr.
• Attributes Calculation:
  • age calculation uses the current date minus date of birth.
  • clinician_name combines the provider’s last and first names.
  • Default values used for primary_payer and financial_class if NULL.

Update billed_date in #enc_list

This additional UPDATE statement sets the billed_date.

UPDATE EL
SET EL.billed_date = CL.billed_date
FROM #enc_list EL
INNER JOIN (
    SELECT C.enc_id,
           MIN(C.create_timestamp) AS billed_date
    FROM dbo.claims C
    INNER JOIN #enc_list EL2 ON EL2.enc_id = C.enc_id
    GROUP BY C.enc_id
) CL ON CL.enc_id = EL.enc_id;

Key Points

• Sub-Query:
  • Selects the minimum create_timestamp as billed_date for each enc_id in the claims table.
• Inner Join: Ensures the billed_date is updated in #enc_list based on matching enc_id.

Summary

This sequence of SQL operations:

• Creates a temporary table for encounter data.
• Populates the table with filtered and aggregated data.
• Updates the table to enrich it with additional linked information from various tables.
• Final update operation to set billing date information.

For further learning, the Enterprise DNA Platform offers courses on advanced SQL, data modeling, and best practices in data integration which can deepen understanding and proficiency in handling similar tasks.