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.
Something like this perhaps:
from (select top(2)
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
SELECT actualdate_max, actualdate_previous
SELECT MAX(actualdate) AS actualdate_max
) AS get_max_date
OUTER APPLY (
SELECT MAX(actualdate) AS actualdate_previous
WHERE actualdate <= DATEADD(DAY, -75, actualdate_max)
) AS oa1
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??
Using ScottPletcher's query add
and actualdate >= DATEADD(DAY, -90, actualdate_max)