query of query for two dates

[Tonyam]

I am trying to query the database for a given date and the next nearest older date within 28 days. This is what I have tried but it isn't assigning anything to DD1 and DD2. i have tried a number od other things but i am stumped.


<cfquery name="getdatedata" datasource="dairy">
SELECT MAX(std.DataDate) as DD1,
MIN(std.DataDate) as DD2,
DateDiff(d, MIN(std.DataDate), MAX(std.DataDate)) AS DDiff
FROM
(select top 2 datadate
FROM dairy.dbo.paddockdata
WHERE FarmId='#form.farmid#'
AND datadate between #datevalue#
and dateAdd("d", -28, #datevalue#)
ORDER by datadate DESC) AS std
</cfquery>

[MikerRoo · Accepted solution]

AND
( datadate between dateAdd("d", -28, #datevalue#) <!--- smallest value first. --->
and #datevalue#
)

[Tonyam]

How simple! Thanks!