Hi All!! Im having trouble with a query that Im hoping to get some help on. I have a very simple table, and all Im trying to do is get the records returned if the max date is 30 days older than the current (todays) date. Except that I don't want any of the records if one is within the 30 days. So in the table, I have two records for "MIPR_ID = 2778" . One has a date of 2/19/2021 and the other has a date of 9/16/2020. So I don't want either record returned because one record is within the current 30 days. However, if both records had a date more than 30 days old, I would want both records returned. Below is my query, but it doesnt work and I am at a loss. Any help would be greatly appreciated.
<cfset doccurrentdate="#DateFormat(Now(), 'dd-mmm-yyyy')#" />
<cfset docprevdate="#doccurrentdate#" -30 />
<cfquery name="CcomInput" datasource="MOracle">
select distinct mipr_id, id,
max(last_remark_update) as lastdate
where max(last_remark_update) < '#dateformat(docprevdate, 'dd-mmm-yyyy')#'
and unob_remarks.mipr_id = '2778'
GROUP BY unob_remarks.mipr_id, unob_remarks.id