I have two tables. Cla_case cc and Cla_event ce. The join between the two tables is on cc.cla_event_no = ce.cla_event_no.
In cla_case i have the following columns : Discover_date and Notification_date. In cla_event I have the column Incident_date.

I need to perform a SUBQUERY that will find the latest date (most recent) when comparing cc.Discover_date and ce.Incident_date. Once it has done this I then need to subtract this record from cc.Notification_date to find out how many DAYS occurred until the incident was reported.

My date format is as follows : 16-APR-2008 07.50
Once again I only require the DAYS between Notification and the latest date between Discover and Incident.

Thank-you very much!

Oracle Database 11g Release - 64bit Production
PL/SQL Release - Production
"CORE Production"
TNS for Linux: Version - Production
NLSRTL Version - Production

Recommended Answers

All 3 Replies

Member Avatar

Okay... This should do the trick.

Select CC.Cla_Event_No
, CC.Discover_Date, CC.Notification_Date, CE.Incident_Date  -- Just to see what they are
, Trunc (CC.Notification_Date) - Greatest (Trunc (CC.Discover_Date), Trunc (CE.Indicent_Date)) "Days"
From Cla_Case CC, Cla_Event CE
Where CC.Cla_Event_No = CE.Cla_Event_No
Order by CC.Cla_Event_No;
commented: Simple and clear, thanx!!! +0

Thank-you very much!!! Appreciate your help!!!

Member Avatar

Please mark this as solved. Thank-You!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.