Syntax for pulling the max date, and then the previous date which has to be at least 75 days from the max date?

Transact-SQL
1

I have a query I am writing where i have to pull the max "actual date" or most recent date and the next most recent date (previous date) which has to be at least 75 days from the most recent date. What would be the syntax in sql for that? The date field is named "actualdate".

I figured the syntax for most recent date would be select max(actualdate) but how would you pull a the next most recent actualdate that has to be at least 75 days from the most recent actualdate? Please help me someone.

2

Something like this perhaps:

Query 
select top(1)
       actualdate
  from (select top(2)
               actualdate
         from yourtable
        order by actualdate desc
       ) as a
 order by actualdate
;

EDIT: This will not work, as I forgot to take into account "at least 75 days from most recent date". Sorry

1 Like
3 
SELECT actualdate_max, actualdate_previous
FROM (
    SELECT MAX(actualdate) AS actualdate_max 
    FROM dbo.table_name
) AS get_max_date
OUTER APPLY (
    SELECT MAX(actualdate) AS actualdate_previous
    FROM dbo.table_name
    WHERE actualdate <= DATEADD(DAY, -75, actualdate_max)
) AS oa1
1 Like
4

Thank you so much but how would I set the query to where it is 75 days from the most recent date but less than 90??

5

Using ScottPletcher's query add
and actualdate >= DATEADD(DAY, -90, actualdate_max)