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

select distinct ea.FIN
, dem.patient_name
, dem.dob
, age_in_years(e.beg_effective_dt_tm::date, dem.dob) as Age_at_Encounter
, cv.display as Facility
, cv2.display as Nurse_unit
, cv3.display as Loc
, cv4.display as Encounter_Type
, e.reason_for_visit
, e.est_arrive_dt_tm
, diag.Source_Vocabulary 
, diag.ICD_10_Code
, diag.ICD_10_Diagnosis
, p.procedure_note
, p.beg_effective_dt_tm
, earliest_rn
from (select *, row_number() over (partition by person_id order by beg_effective_dt_tm asc) earliest_rn from encounter
where est_arrive_dt_tm::date >= '2018-01-01' 
and est_arrive_dt_tm::date <= '2021-12-31') e
left join code_value cv on e.loc_facility_cd = cv.code_value
left join code_value cv2 on e.loc_nurse_unit_cd = cv2.code_value
left join code_value cv3 on e.location_cd = cv3.code_value
left join code_value cv4 on e.encntr_type_cd = cv4.code_value
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 * from procedure) p on diag.encntr_id = p.encntr_id
inner join (select distinct * from demographic where mrn_rownum = 1 and phone_rownum = 1 and address_rownum = 1) dem on e.person_id = dem.person_id
where loc_facility_cd = 11869 
and earliest_rn = 1

Currently, the earliest_rn column displays a number for each person_id. In addition to this, I would like to see the beg_effective_dt_tm that corresponds to the earliest_rn, in the partition by. I am trying to find the first (earliest date) encounter with a column showing that exact date (beg_effective_dt_tm) for each person_id.

In Oracle/Postgres it is possible to write a script that will run other scripts/files such as compile_script.sql (contains) @script1.sql @script2.sql @script3.sql When you run compile_script.sql it runs all three of those scripts. In snowSQL (command line tool for SnowFlake) you can run the sql in a file with a !source compile_script.sql but I am unable to determine how I can have that run other files as I can do in Oracle/Postgres. How would I go about running all of or part of the scripts in a directory without having to run each of them by hand?