Please see the attached witch shows
fig1. current output from tbl_assessments
fig2. desired output from the same table
I want the output to show an additional column (first_assessment_id). This will get the assessment ID from the assessment with the earliest end_date for the person and repeat it for every subsequent assessment for the person. I tried something like
min(ssca_id) over (partition by ssca_dim_person_id order by ssca_dim_person_id, ssca_end_date asc)
but found this didn’t work as the earliest assessment_id recorded for the person was not always a lower number than subsequent assessments for the person
any help is appreciated.