Extract Target Diagnostic Codes:

Step 1: Table Segmentation and Initialization

Other than spending time studying and knowing each of the tables well,  to manage the database effectively, we first need to create intermediate tables and standardize the structures.

• Create Intermediate Tables for Each Time Period:
  • Extract raw data into separate tables based on the time periods (2005–2012, 2013–2015, 2016–2018) and patient types (inpatient and outpatient).
  • Ensure each intermediate table includes relevant columns for initial analysis.
• Standardize Column Names and Order:
  • Align column names and ensure consistent ordering across all tables to facilitate merging and subsequent processing.
  • Use a predefined schema to normalize structural discrepancies.
• Filter by Target Diagnostic Codes:
  • Apply filtering criteria to retain only records matching the specified diagnostic codes.

These steps create 6 clean and consistent intermediate tables, ready for further processing and standardization.

Step 2: Standardizing Table Structures

To address structural variations and ensure consistency across tables:

1. Create Unified Column Names and Formats:
   • Align column names and data types across all tables to match a standardized schema.
   • Ensure columns such as patient_id, state_code, diagnostic_code, and service_date are consistently formatted.
2. Compute Min and Max Values for Validation:
   • For demographic columns (e.g., dates, gender, birthdate), calculate the minimum and maximum values.
   • These metrics will help identify outliers and ensure data validity in later steps.
3. Merge Diagnostic Codes into a Single String:
   • Use a concatenation process to consolidate multiple diagnostic codes for a single patient.
   • This ensures compatibility when merging records

Step 3: Combining Tables

To unify data across inpatient and outpatient records from each year:

• Create a combine table:
  • Create a new table with all necessary columns.
  • Insert the corresponding data from the 6 tables from the previous step into the new create table.
• Validate Merged Data:
  • Check the row number to ensure data integrity

Step 4: Validating Data Consistency

To ensure data accuracy before applying the two-year condition:

1. Compare Min and Max Values:
   • Identify records where the min and max values for a column are inconsistent.
   • Flag these records with missing or conflicting data (Max =！Min).
2. Clean Data:
   • Manually work a case study to analyze the problems
   • Clean the data with problem and move on to next step

Step 5: Applying the Two-Year Condition

As the final step, determine if patients meet the two-year diagnostic criteria:

1. Identify Records Meeting Criteria:
   • For each patient, check if:
     • At least two different diagnostic or medication criteria are satisfied within a 24-month period.
     • Alternatively, the same criterion is satisfied twice on separate dates within the 24-month period (this can apply the same coding logic together with the previous one)

Loops and conditional logic were implemented (as the most quick method) to iterate through data records for identifying patients with diagnostic codes that appear within a two-year window. For each patient:

• Fetch their records ordered by time.
• Compare each new record’s service date with the previous record’s date.
• If the difference in days is less than or equal to two years, mark the record as satisfying the constraint.

2. Select the target patient population:
   • Create a table that only stores all the distinct patient_id which pass all of our conditions.