I'm having one heck of a time trying to get a query to work. What I want to do is this:
I have to pull data from one table and join to second table. I am looking for a specific value and date in the second table, that will not always appear in every ticket.
In other words. there are 30 tickets, 4 of them have a SWAT value occuring sometime during that ticket. I want to show all 30 tickets with the 4 showing the last date when there was a SWAT value in those particular tickets.

Here is what I've written (and rewritten several times) It doesn't produce the result I'm looking for.

I keep getting multiple rows with multiple values. I am also getting just one date back that is populating in every ticket!

SELECT
hpd.CASE_ID CaseID,
to_char(to_date('31-DEC-1969 17:00','DD-MON-YYYY hh24:mi') + (auditdate.aud_date/(60*60*24)),'DD-MON-YYYY hh24:mi') audit_date
FROM
ARADMIN.HPD_HELPDESK hpd,
(SELECT FIRST_VALUE(Audit_Date) OVER (ORDER BY HD_CASE_ID DESC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) As aud_date,HD_CASE_ID FROM ARADMIN.HPD_audit_ACTIVITY_LOG WHERE EXISTS(SELECT Audit_Date FROM ARADMIN.HPD_audit_ACTIVITY_LOG WHERE ARADMIN.HPD_audit_ACTIVITY_LOG.current_status = 'SWAT' OR ARADMIN.HPD_audit_ACTIVITY_LOG.prev_status = 'Swat') GROUP BY Audit_Date,HD_CASE_ID) auditdate
WHERE
AND hpd.CASE_ID=auditdate.HD_CASE_ID(+)
/