asked on

Question with Oracle / Coldfusion SQL Query based on max date

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

from MCA.unob_remarks

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
</cfquery>

slightwv (䄆 Netminder)

You don't say what "doesn't work" means.

If you are getting a syntax error, it's probably because you can't use max in the where clause.

See if this works for you:

select * from (
	select mipr_id, id,
		max(last_remark_update) as lastdate
	from MCA.unob_remarks
	GROUP BY unob_remarks.mipr_id, unob_remarks.id
)
where lastdate < '#dateformat(docprevdate, 'dd-mmm-yyyy')#'
	and unob_remarks.mipr_id = '2778'

Open in new window

If you mean something else, please explain what "doesn't work" means.

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

diecasthft01

ASKER

Thanks for the info / query examples. Im afraid neither example worked for me. It's still returning the MIPR_ID record where the date is within the 30 days of sysdate.

Just for info, Im trying to feed this query into a report, and that report basically has several updates to a given MIPR_ID. The report will show the MIPR_ID only if one of the many last_remark_update date entries are outside of the last 30 days. If just one or more of the remarks are within the last 30 days, then the MIPR_ID does not show on the report at all. Its basically just a report to see which MIPR_IDs do not have a recent update done to it.

slightwv (䄆 Netminder)

Cannot help with the CF aspect.

Here's my database test:

/*

drop table tab1 purge;
create table tab1(mipr_id number, id number, last_remark_update date);

insert into tab1 values(2778,1,to_date('2/19/2021','MM/DD/YYYY'));
insert into tab1 values(2778,1,to_date('9/16/2020','MM/DD/YYYY'));

insert into tab1 values(2778,2,to_date('2/19/1900','MM/DD/YYYY'));
insert into tab1 values(2778,2,to_date('9/16/1900','MM/DD/YYYY'));
commit;

*/

select * from (
	select mipr_id, id,
		max(last_remark_update) as lastdate
	from tab1
	GROUP BY mipr_id, id
)
where lastdate < sysdate-30
and mipr_id = '2778'    
;

Open in new window

My result:

   MIPR_ID         ID LASTDATE
---------- ---------- -------------------
      2778          2 09/16/1900 00:00:00

Open in new window

Can you add to the test setup so show where it doesn't give you what you want?

Kent Olsen

Hi Diecast.

My sample works for me. Here's the entire test:

CREATE TABLE T (user_id number, cdate date);

INSERT INTO T SELECT 2778, '19-FEB-21' FROM Dual;
INSERT INTO T select 2778, '16-SEP-20' FROM dual;
COMMIt;

SELECT * FROM T;

SELECT * FROM t t0
INNER JOIN
(
SELECT user_id, max(cdate) last_date
FROM t
GROUP BY user_id
HAVING max(cdate) < sysdate - 30
) t1
ON t0.user_id = t1.user_id;

diecasthft01

ASKER

So your result is my result.....Im getting that same record. But I want a result of null, because one of the two dates are within the 30 date window. Maybe this cant be done but I was thinking I could have a select where if one of the dates are within 30 days, select none of the records.

slightwv (䄆 Netminder)

>>But I want a result of null, because one of the two dates are within the 30 date window.

Isn't that what my results show?

In my test id=1 has one row within 30 days. id=2 doesn't. My result only shows id=2?

diecasthft01

ASKER

Im looking for a null result. No records selected because one of the two records has a date within 30 days.

slightwv (䄆 Netminder)

I also get no rows from Kent's test.

If I change Kent's test to use really old dates, I see the rows.

What are we missing in our tests?

diecasthft01

ASKER

The query is returning 1 record / result.....because one of the two is outside of the 30 days.

slightwv (䄆 Netminder)

>>Im looking for a null result. No records selected because one of the two records has a date within 30 days.

So, null values but a rows returned instead of "no rows returned"?

diecasthft01

ASKER

let me check again on Kents query.

slightwv (䄆 Netminder)

If you want a row with null values, what columns are null?

slightwv (䄆 Netminder)

>>let me check again on Kents query.

Mine only accesses the table once.

diecasthft01

ASKER

Maybe theres something with my table data.Let me do a new test table exactly like your tests and go from there.

slightwv (䄆 Netminder)

Reminder:
The accepted solution is doing more work than is necessary and I don't see a difference in results.