Hello all
One big thing which I am struggling with at the moment is using MAX in scripts where I am trying to find the latest status in a status history table based on the date field or a primary key field. I show an example below where it doesn't return any results however when I don't use the MAX function, I get two results for statuses over 3 years ago. Can anyone see what is wrong in my script?
SELECT client_ID, crms_number, ...
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY client_ID, crms_number
ORDER BY date_modified DESC) AS row_num
FROM dbo.ZZ_status_history
WHERE client_ID = 'XXX' and crms_number = XXX and
date_modified <DATEADD(month, datediff(month, 0, @ysM3), 0)
) AS derived
WHERE row_num = 1
Thanks Scott, if I didn't want to have a date parameter how could I adapt this query - to just show the latest status?
I have tried the below but it returns an error:
SELECT client_id, crms_number, student_status, date_modified FROM (
select *, ROW_NUMBER() OVER(PARTITION BY client_ID, crms_number ORDER BY date_modified DESC) AS row_num
FROM dbo.ZZ_crms_student_status_history
WHERE row_num = 1
Just remove the date test from the WHERE condition:
SELECT client_ID, crms_number, ...
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY client_ID, crms_number
ORDER BY date_modified DESC) AS row_num
FROM dbo.ZZ_status_history
WHERE client_ID = 'XXX' and crms_number = XXX
) AS derived
WHERE row_num = 1
--ORDER BY client_ID, ...