How to have another column showing first date from partiton by clause?
1
vote
0
answers
47
views
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.
Asked by Michelle
(21 rep)
Jul 13, 2023, 02:35 PM
Last activity: Jul 17, 2023, 01:23 PM
Last activity: Jul 17, 2023, 01:23 PM