Link to home
Start Free TrialLog in
Avatar of tim_Burgess
tim_Burgess

asked on

MSSQL - Analytic function help

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.
sample-data-20181025.xlsx
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tim_Burgess
tim_Burgess

ASKER

thankyou
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thankyou awking00, thats a neat solution
uw :-)