SQLTeam.com | Weblogs | Forums

Finding the status based on a date field in the results


#1

Hello all

The below query finds a pool of records for which I then need to find a status from a status history table based on the "DateTagCreated" result created for each record (coming from a different table).

I had thought I might be able to use declare or a subquery, does anyone have any help for this query please?

SELECT
c.client_name,
c.client_id,
sth.crms_number,
sth.tag_value,
sth.date_created [DateTagCreated],
ROW_NUMBER() OVER (PARTITION BY
c.client_name, sth.crms_number
ORDER BY c.client_name, sth.crms_number, sth.date_created ASC) seq_rev
FROM dbo.zz_crms_student_tag_history STH

LEFT JOIN dbo.zz_client C
ON sth.client_id = c.client_id

LEFT JOIN dbo.zz_crms_student S
ON sth.client_id = s.client_id
AND sth.crms_number = s.crms_number

Thanks in advance