Limiting query results by date

Question

I have a working query constructed by multiple Join clauses. One of the columns is RecordDate. I want to display only records under the following condition: RecordDate > ((Max(RecordDate) for the whole report)-40) days. In other words, records dating Max date or 40 days earlier. I noticed that I cannot calculate the date-40 in the WHERE clause and so I have created a date_CTE query to give me the calculated "old-date".

WITH date_CTE(PrevDate,sd) AS
    (SELECT dateadd(DAY,-33,max(ScanDate)),ScanDate
    FROM v_TthreatsCombined
    GROUP BY scanDate)
    SELECT MAX(prevDate) FROM date_CTE

I have failed to join the result of "SELECT MAX(prevDate) FROM date_CTE" into WHERE.

Query looks a little like this:

SELECT col1,col2.col3,ScanDate
FROM table1
Left Join ....
Left Join ...
WHERE condition1 AND (condition2 OR condition3) AND <SELECT MAX(prevDate) FROM date_CTE>

Am I at all on the right path? Thank you for helping.

Amos

Answer 1

One option uses window functions. Based on your pseudo-code, this would look like:

select *
from (
    select col1, col2, col3, scandate, max(scanDate) over() maxScanDate
    from table1
    left join ....
    left join ...
    where condition1 and (condition2 or condition3)
) t
where scanDate > dateadd(day, -33,  maxScanDate)