Date Based Query off the Max of the Date

[F15]

I need to write a query which identifies the latest date in the database and then goes back 3 months. In other words, if the latest data is for October 2002, then I need the data for August, September and October. When the data is updated with November data, then the query should pick up September, October, and November data.

I have tried the following SQL:

SELECT Sum(Daily.Amount) AS SumOfAmount
FROM Daily
WHERE (((Monthly.Date) Between DateAdd("m",-3,Date()) And Date()));
[code]
but before the November data is loaded, this will only pick up Sept and Oct data if it is currently November.

Can I include the Max function within the DateAdd function?
Thanks!

 

[mpcohen]

This looks like a SQL Server question rather than a BRIO question. You can include the max date in a sub-query.

You would write:

select Sum(Daily.Amount) AS SumOfAmount
from Daily
where
Daily.Date >= (select DateAdd("m",-3, max(Daily.Date))
from Daily)

 

[F15]

Thanks for your suggestion. This has worked very well. I appreciate the help.