Data Structure Designer

Data Structure Design for Reporting and Queries

This document outlines the design of temporary data structures for efficient querying and reporting from a task database, highlighting the use of hash tables, arrays, and tuples for streamlined data management.

Prompt

DROP TABLE if exists #TASKS
SELECT DISTINCT
tm.task_id
,tm.practice_id
,ptenc.enc_nbr
,case
    when c.cob1_amt <> 0 then ml1.mstr_list_item_desc
    when c.cob2_amt <> 0 then ml2.mstr_list_item_desc
    when c.cob3_amt <> 0 then ml3.mstr_list_item_desc else 'Patient'
end as 'Current_Fin_Class'
,case
    when c.cob1_amt <> 0 then pm1.payer_name
    when c.cob2_amt <> 0 then pm2.payer_name
    when c.cob3_amt <> 0 then pm3.payer_name else 'Patient'
end as 'Current_Payer'
,case
    when c.cob1_amt <> 0 then '1-Primary'
    when c.cob2_amt <> 0 then '2-Secondary'
    when c.cob3_amt <> 0 then '3-Tertiary' else '4-Patient'
end as 'Current_COB'
,c2.tot_chg_amt as Tot_Chg_Amt
,eb.enc_balance as Enc_Balance
,tm.assigned_to_display_only as Assigned_To
,tm.completed_by_user_id as Cmp_By
,tm.completion_reason as Comp_Reason
,tm.created_by as Created_By
,tm.modified_by as Modified_By
,tm.subject as Subject
,CASE WHEN tm.task_status = 1 THEN 'Not Started'
  WHEN tm.task_status = 2 THEN 'In Progress'
  WHEN tm.task_status = 2 THEN 'Completed'
ELSE NULL
END as Status 
,CASE WHEN tm.task_priority = 0 THEN 'Low'
  WHEN tm.task_priority = 1 THEN 'Normal'
  WHEN tm.task_priority = 2 THEN 'High'
ELSE NULL
END as Priority_Desc
,ml.mstr_list_item_desc as 'Task_Subgrouping1_Name'
,tm.task_subgrouping1_id as Task_Subgrouping_1
,tm.task_subgrouping2_id as Task_Subgrouping_2
,ttm.description as Type_Desc
,tex.encounter_id
,CONVERT(DATE,tm.create_timestamp) as Create_Dt
,CONVERT(DATE,tm.followup_date) as Fup_Dt
,CONVERT(DATE,tm.due_date) as Due_Dt
,CONVERT(DATE,tm.completion_date) as Comp_Dt
,CONVERT(DATE,tm.modify_timestamp) as Mod_Dt
INTO #TASKS
  FROM dbo.task_mstr tm
  LEFT JOIN dbo.task_encounter_xref tex on tex.task_id = tm.task_id  
  JOIN dbo.task_type_mstr ttm on ttm.task_type_id = tm.task_type_id
  JOIN mstr_lists ml on ml.mstr_list_item_id = tm.task_subgrouping1_id
  LEFT JOIN patient_encounter ptenc on ptenc.enc_id = tex.encounter_id


LEFT JOIN charges c on c.practice_id=ptenc.practice_id and c.source_id=ptenc.enc_id

LEFT JOIN encounter_payer ep1 on ptenc.practice_id=ep1.practice_id and ptenc.enc_id=ep1.enc_id and ep1.cob = 1
LEFT JOIN payer_mstr pm1 on ep1.payer_id=pm1.payer_id
LEFT JOIN mstr_lists ml1 on pm1.financial_class=ml1.mstr_list_item_id
LEFT JOIN encounter_payer ep2 on ptenc.practice_id=ep2.practice_id and ptenc.enc_id=ep2.enc_id and ep2.cob = 2
LEFT JOIN payer_mstr pm2 on ep2.payer_id=pm2.payer_id
LEFT JOIN mstr_lists ml2 on pm2.financial_class=ml2.mstr_list_item_id
LEFT JOIN encounter_payer ep3 on ptenc.practice_id=ep3.practice_id and ptenc.enc_id=ep3.enc_id and ep3.cob = 3
LEFT JOIN payer_mstr pm3 on ep3.payer_id=pm3.payer_id
LEFT JOIN mstr_lists ml3 on pm3.financial_class=ml3.mstr_list_item_id
LEFT JOIN (select source_id, sum(amt) as tot_chg_amt from charges group by source_id) c2 on ptenc.enc_id=c2.source_id
LEFT JOIN (select practice_id, source_id, sum(cob1_amt+cob2_amt+cob3_amt+pat_amt) as enc_balance, sum(cob1_amt+cob2_amt+cob3_amt) as enc_ins_balance from charges where (cob1_amt+cob2_amt+cob3_amt+pat_amt <> 0) group by practice_id, source_id having sum(cob1_amt+cob2_amt+cob3_amt+pat_amt) > 0) eb on c.practice_id=eb.practice_id and c.source_id=eb.source_id
WHERE DATEPART(YYYY,CONVERT(DATE,tm.create_timestamp)) = DATEPART(YYYY,GETDATE())
and case
    when c.cob1_amt <> 0 then ml1.mstr_list_item_desc
    when c.cob2_amt <> 0 then ml2.mstr_list_item_desc
    when c.cob3_amt <> 0 then ml3.mstr_list_item_desc else 'Patient' end not in  ('Patient')

AND ml.mstr_list_item_desc in (
                                'CO - Addendum/Justification Needed',
                                'CO - Auth Invalid/Missing',
                                'CO - COB/Payer Change',
                                'CO - Coding/Units Issue',
                                'CO - Demographics Issue',
                                'CO - DOS Change',
                                'CO - DX Code Invalid/Missing',
                                'CO - Location Change',
                                'CO - Measurement Form',
                                'CO - Medical Records',
                                'CO - Modifier Invalid/Missing',
                                'CO - Narrative',
                                'CO - New Encounter Needed',
                                'CO - PECOS/OPR Enrollment',
                                'CO - PO Required',
                                'CO - Questionnaire Form',
                                'CO - SWO/Rx Invalid/Missing',
                                'CO - Void Charges In PM',
                                'CO - VOR Invalid/Missing')
DROP TABLE if exists #LP
SELECT DISTINCT
t.task_id ,p.practice_name as Prac_Name ,lm.Location_Name as Loc_Name
,per.first_name + ' ' + per.last_name as Name 

INTO #LP
  FROM #TASKS t 
  LEFT JOIN dbo.practice p on p.practice_id = t.practice_id
  LEFT JOIN dbo.task_encounter_xref tex on tex.task_id = t.task_id
  LEFT JOIN dbo.person per on per.person_id = tex.person_id
  LEFT JOIN dbo.patient_encounter pe on pe.enc_id = tex.encounter_id
  LEFT JOIN dbo.location_mstr lm on lm.location_id = pe.location_id 


DROP TABLE if exists #MSTR
SELECT DISTINCT
t.task_id, mls1.mstr_list_item_desc as Task_Subgrouping_1, mls2.mstr_list_item_desc as Task_Subgrouping_2, mlc.mstr_list_item_desc as Comp_Reason
INTO #MSTR
  FROM #TASKS t 
  LEFT JOIN dbo.mstr_lists mlc on mlc.mstr_list_item_id = t.Comp_Reason
  LEFT JOIN dbo.mstr_lists mls1 on mls1.mstr_list_item_id = t.Task_Subgrouping_1
  LEFT JOIN dbo.mstr_lists mls2 on mls2.mstr_list_item_id = t.Task_Subgrouping_2


DROP TABLE if exists #USER
SELECT user_id
INTO #USER
  FROM (
SELECT DISTINCT t.Created_By as user_id FROM #TASKS t
UNION
SELECT DISTINCT t.Modified_By as user_id FROM #TASKS t
UNION
SELECT DISTINCT t.Cmp_By as user_id FROM #TASKS t)A


DROP TABLE if exists #USERF
SELECT
user_id
,user_name
INTO #USERF
FROM (
SELECT
u.user_id 
,umc.last_name + ', ' + umc.first_name as user_name
   FROM  #USER u 
   JOIN dbo.user_mstr umc on umc.user_id = u.user_id
)D

SELECT 
 t.task_id
,t.enc_nbr
,l.Prac_Name
,l.Loc_Name
,l.Name
,t.Assigned_To
,ud.user_name as  Cmp_By
,m.Comp_Reason
,uc.user_name as Created_By
,um.user_name as Modified_By
,t.Subject
,t.Status
,t.Priority_Desc
,m.Task_Subgrouping_1
,m.Task_Subgrouping_2
,t.Type_Desc
,t.Create_Dt
,DATEDIFF(DD, t.Create_Dt, GETDATE()) as Create_Age
,t.Fup_Dt
,CASE WHEN Fup_Dt LIKE '1900-01-01' THEN '' ELSE DATEDIFF(DD, t.Fup_Dt, GETDATE()) end as Fup_Date_Age
,t.Due_Dt
,t.Comp_Dt
,t.Mod_Dt
,t.Current_COB
,t.Current_Fin_Class
,t.Current_Payer
,t.Tot_Chg_Amt
,t.Enc_Balance
  FROM #TASKS t
  LEFT JOIN #LP l on l.task_id = t.task_id
  LEFT JOIN #MSTR m on m.task_id = t.task_id
  LEFT JOIN #USERF uc on uc.user_id = t.Created_By
  LEFT JOIN #USERF um on um.user_id = t.Modified_By
  LEFT JOIN #USERF ud on ud.user_id = t.Cmp_By

Answer

Recommendation and Data Structure Design

Problem Analysis

The problem involves querying and restructuring data from various database tables to create temporary tables that consolidate this information for specific use cases. The data structure must account for efficient grouping, joining, and filtering to produce a final report.

Requirements:

Extract tasks data (task_mstr) joined with several related tables.
Group data by different criteria and store it temporarily (#TASKS, #LP, #MSTR, #USER, #USERF).
Perform complex conditional logic for certain fields.
Generate a final report consolidating all the information from these temporary tables.

Data Structure Selection:

Main Considerations:

Efficiency of JOIN operations: Requires efficient lookup and merge operations.
Conditional Logic: Data structures that allow straightforward conditional queries.
Temporary Storage: Data structures that support temporary, in-memory storage to avoid permanent database modifications.

Recommended Data Structures:

Hash Tables (Dictionaries/Maps)
- Use Case: Efficiently map and retrieve detailed information about tasks, practice details, payers, etc.
- Structure: Key-value pairs, where keys are unique identifiers like task_id.
Arrays/Lists
- Use Case: Store collections of items temporarily for sequential operations and aggregations.
- Structure: Ordered collections to process data linearly.
Tuples/Records (Structs/NamedTuples)
- Use Case: Immutable, structured groupings of related data items.
- Structure: Fixed-size, heterogeneous collection of data fields.

Template Generation:

1. Hash Tables

Description

Hash tables are suitable for quick data lookups, such as matching task_id with task details.

Structure:
- Key: Unique identifier (e.g., task_id)
- Value: Data record/tuple containing multiple fields

Example (Pseudocode):
task_details = {
    task_id: {
        'practice_id': practice_id,
        'enc_nbr': enc_nbr,
        'Current_Fin_Class': current_fin_class, 
        'Current_Payer': current_payer,
        ...
    }
}

2. Arrays/Lists

Description

Arrays or lists are useful for storing sequences of tasks and related data for iteration and aggregation operations.

Structure:
- Each element: Individual data record or tuple
- Operations: Iteration, aggregation, filtering

Example (Pseudocode):
tasks = [
    (task_id, practice_id, enc_nbr, current_fin_class, ...),
    (task_id, practice_id, enc_nbr, current_fin_class, ...),
    ...
]

3. Tuples/Records

Description

Tuples or records provide an immutable, structured way to store multiple related data fields.

Structure:
- Fixed-size collection of related fields
- Immutable: Once created, the data cannot be changed

Example (Pseudocode):
Task = (task_id, practice_id, enc_nbr, current_fin_class, ...)
task_record = Task(task_id, practice_id, enc_nbr, current_fin_class, ...)

Description and Documentation

Hash Tables

Usage

Purpose: For efficient querying and updating individual records by task_id.
Example Operations: Insert, Update, Retrieve by key.

Template

# Pseudocode representation
hash_table = {}
# Insert operation
hash_table[task_id] = {
    'practice_id': practice_id,
    'enc_nbr': enc_nbr,
    'Current_Fin_Class': current_fin_class, 
    'Current_Payer': current_payer,
    # additional fields
}

# Lookup operation
record = hash_table[task_id]

Arrays/Lists

Usage

Purpose: For storing collections of tasks to be processed sequentially.
Example Operations: Iteration, Filtering, Aggregation.

Template

# Pseudocode representation
task_list = []

# Append operation
task_list.append((task_id, practice_id, enc_nbr, current_fin_class, ...))

# Iteration example
for task in task_list:
    process(task)

Tuples/Records

Usage

Purpose: For representing structured data that doesn't change over time.
Example Operations: Creation, Access.

Template

# Pseudocode representation
Task = namedtuple('Task', ['task_id', 'practice_id', 'enc_nbr', 'current_fin_class', ...])

# Creation
task_record = Task(task_id, practice_id, enc_nbr, current_fin_class, ...)

# Access
print(task_record.task_id)  # Accessing the task_id field

For more in-depth learning on data management and advanced data structures, consider courses available on the Enterprise DNA platform.