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

5 Years
Discussion Span
Last Post by hfx642

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;

Edited by hfx642: n/a

Votes + Comments
Simple and clear, thanx!!!
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.