MSSQL - Analytic function help

tim_Burgess
tim_Burgess used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
you will need to do something like:
SELECT MIN( ssca_end_date  ) AS ssca_end_date , ssca_id
FROM YourTable
GROUP BY ssca_id

Open in new window


and make sure indexes exist on both columns - ssca_end_date in particular to support implicit sorting needed by MIN function

Author

Commented:
thankyou
awking00Information Technology Specialist
Commented:
If you are using sql server 2012 or later, you can us the first_value() function to get what you want -
select first_value(assessment_id) over (partition by person_name order by end_date) first_assessment_id, assessment_id,
person_id, person_name, end_date
from yourtable;

Author

Commented:
thankyou awking00, thats a neat solution
awking00Information Technology Specialist

Commented:
uw :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial