SQLTeam.com | Weblogs | Forums

Using MAX to identify the latest status


#1

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?

Thanks in advance

DECLARE @now datetime = getDate()
DECLARE @ms datetime = dateadd(month, datediff(month, 0, @now), 0)
DECLARE @ysM3 datetime = dateadd(year, -3, @ms)

SELECT * FROM dbo.ZZ_status_history
WHERE client_ID = 'XXX' and crms_number = XXX

and date_modified=(select MAX(date_modified) from dbo.ZZ_status_history
where date_modified <DATEADD(month, datediff(month, 0, @ysM3), 0))


#2
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

#3
@ms        @ysM3
---------- ----------
2016-10-01 2013-10-01

So in effect:

select MAX(date_modified) from dbo.ZZ_status_history 
where date_modified < '20131001'

which is going to be the most recent date in [ZZ_status_history] earlier than 2013-10-01 - let's call that CUTOFF_DATE

and then that date will be matched with any row(s):

SELECT * FROM dbo.ZZ_status_history
WHERE client_ID = 'XXX' and crms_number = XXX
and date_modified = CUTOFF_DATE

#4

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


#5

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, ...

#6

Thanks so much, Scott! :slight_smile:


#7

If you are on 2012 or higher - look at using LAST_VALUE which will return the last value based on the window you provide.


#8

Thanks Jeff bu sadly on 2008 :frowning: