creating a CTE to filter previous CTE populaiton down. How to find new diagnosis of certain ICD 10 code?

Question

The first CTE grabs all patients with the diagnosis codes for substance abuse, the second CTE should filter down to only NEW paitents. Meaning of the people in the first CTE, which of thses people have noy been diagnosised with the 2 codes before. The 3rd CTE grabs all information about the person, locations, date, and age.

In need of figuring out the logic for the 2nd CTE: from diag ... only people who havent been diagnosed before (ie. new/inital diagnosis)

with diag as (
select distinct d.person_id
, d.encntr_id
, d.diagnosis_id
, d.nomenclature_id
, d.diag_dt_tm
, d.diag_prsnl_name
, d.diagnosis_display
, cv.display as Source_Vocabulary 
, nm.source_identifier as ICD_10_Code
, nm.source_string as ICD_10_Diagnosis
, nm.concept_cki 
from wny_prod.diagnosis d 
inner join (select * from nomenclature
        where source_vocabulary_cd in (151782752, 151782747) --ICD 10 CM and PCS
        and (source_identifier ilike 'F10.10' or source_identifier ilike 'F19.10')) nm on d.nomenclature_id = nm.nomenclature_id
left join code_value cv on nm.source_vocabulary_cd = cv.code_value 
)
diag_final as (
select * 
from diag
--?
)
select distinct ea.FIN
, dem.patient_name
, dem.dob
, age_in_years(e.beg_effective_dt_tm::date, dem.dob) as Age_at_Encounter
, amb.location_name
, e.beg_effective_dt_tm
, diag.Source_Vocabulary 
, diag.ICD_10_Code
, diag.ICD_10_Diagnosis
from (select * from encounter where year(beg_effective_dt_tm) = 2022) e 
left join (select distinct encntr_id, alias as FIN from encntr_alias where encntr_alias_type_cd = 844) ea on e.encntr_id = ea.encntr_id
inner join diag on e.encntr_id = diag.encntr_id
inner join (select distinct * from DEMOGRAPHICS where mrn_rownum = 1 and phone_rownum = 1 and address_rownum = 1) dem on e.person_id = dem.person_id
inner join locations_amb amb on amb.loc_facility_cd = e.loc_facility_cd
where age_in_years(e.beg_effective_dt_tm::date, dem.dob) > 13

MackM · Answer 1 · 2024-05-22T04:14:30.627

CREATE OR REPLACE TEMP TABLE EXAMPLE_DATA (person_id INT, diag_dt_tm TIMESTAMP_NTZ(9));
INSERT INTO EXAMPLE_DATA (person_id, diag_dt_tm)
VALUES (1, '2000-01-01'), -- 1 has 3 appearances
       (1, '2000-01-02'),
       (1, '2000-01-03'),
       (2, '2000-01-04'), -- 2 has 2 records for 1 appearance
       (2, '2000-01-04'),
       (3, '2000-01-05'), -- 3 has 1 appearance
       (4, '2000-01-06'); -- 4 has 1 appearance

You can find who has had the relevant diagnosis codes more than once with a GROUP BY. Note that the DISTINCT in here means multiple records for the same diag_dt_tm count as one record. person_id=2 in our example data has 2 appearances, but only one DISTINCT appearance, so person_id=2 is returned.

SELECT person_id
FROM EXAMPLE_DATA
GROUP BY person_id
HAVING COUNT(DISTINCT diag_dt_tm) = 1;
PERSON_ID|
---------+
        2|
        3|
        4|

Now we just join back on ourselves to get the full record.

WITH new_patients AS
   (SELECT person_id
    FROM EXAMPLE_DATA
    GROUP BY person_id
    HAVING COUNT(DISTINCT diag_dt_tm) = 1)
SELECT ED.*
FROM EXAMPLE_DATA ED
RIGHT JOIN new_patients ON ED.person_id = new_patients.person_id;
PERSON_ID|DIAG_DT_TM             |
---------+-----------------------+
        2|2000-01-04 00:00:00.000|
        2|2000-01-04 00:00:00.000|
        3|2000-01-05 00:00:00.000|
        4|2000-01-06 00:00:00.000|

creating a CTE to filter previous CTE populaiton down. How to find new diagnosis of certain ICD 10 code?

1 Answers1