troubleshooting Question

MSSQL - Analytic function help

Avatar of tim_Burgess
tim_Burgess asked on
Microsoft SQL ServerSQL
5 Comments2 Solutions64 ViewsLast Modified:
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
lcohan
Database Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros