Getting the date before the max date

UDA

Getting the date before the max date

Hi,

I'm trying to get the date before the maximum date of my data.

For example, I have 4 rows of data:

22/03/2009
24/03/2009
21/03/2009
25/03/2009

the max date is of course 25/03/2009. but how do i get the date before it, in this case, 24/03/2009?

Thanks in advance for your help.
1 REPLY
Enthusiast

Re: Getting the date before the max date

SEL
*
FROM Your_database.Your_Table
QUALIFY ROW_NUMBER() OVER(PARTITION BY Primary_key_columns ORDER BY date_column DESC ) =2;

if you expect multiple records for the "one less than maximum date" then you can replace row_number with Rank() i.e.
QUALIFY Rank() OVER(PARTITION BY Primary_key_columns ORDER BY date_column DESC ) =2;

but make sure u partition on correct PK cols...

Cheers,
novice