SELECT j.id, NVL2(ssv.name, ssv.name, odv.name) AS object_name, j.created_date, issue_from, CONCAT(CONCAT(su1.first_name, ' '), su1.last_name) AS created_by_name, CONCAT(CONCAT(su2.first_name, ' '), su2.last_name) AS assigned_by_name, j.customer_case, j.issue_type_id, CASE WHEN SUBSTR(t3.ccc, 1, 4000) LIKE '%Ticket is created%' THEN 'Open' WHEN SUBSTR(t3.ccc, 1, 4000) LIKE 'From - to %' THEN 'Assigned' ELSE SUBSTR(t3.ccc, 1, 4000) END AS status, t3.created_date FROM jira_mini j JOIN ( SELECT jma.jira_mini_id, SUBSTR(jma.action_content, 1, 4000) AS ccc, jma.created_date, jma.action_type_id FROM jira_mini_action jma WHERE jma.created_date >= TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS') AND jma.created_date < ADD_MONTHS(TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS'), 1) AND (jma.action_type_id IN (1, 5)) AND jma.jira_mini_id IN ( SELECT jm.id FROM jira_mini jm WHERE (jm.issue_from LIKE 'APEX500' OR (jm.issue_from NOT LIKE 'APEX500' AND jm.assigned_by IS NOT NULL)) AND jm.created_date >= TO_DATE('2016-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS') ) UNION SELECT a.jira_mini_id, SUBSTR(a.action_content, 1, 4000) AS ccc, a.created_date, a.action_type_id FROM jira_mini_action a WHERE a.created_date = ( SELECT MAX(b.created_date) FROM jira_mini_action b WHERE (b.action_type_id IN (1, 5)) AND b.created_date < TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS') AND a.jira_mini_id = b.jira_mini_id ) AND (a.action_type_id IN (1, 5)) AND SUBSTR(a.action_content, 1, 4000) <> 'Close' AND a.jira_mini_id NOT IN ( SELECT jm.id FROM data_management.jira_mini jm JOIN ( SELECT jma.jira_mini_id, MAX(jma.created_date) AS max2 FROM data_management.jira_mini_action jma WHERE jma.action_type_id = 1 AND SUBSTR(jma.action_content, 1, 4000) = 'Close' AND jma.created_date < TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS') GROUP BY jma.jira_mini_id ) t2 ON jm.id = t2.jira_mini_id WHERE jm.assigned_by IS NOT NULL AND jm.issue_status_id = 8 AND jm.created_date < TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS') ) AND a.jira_mini_id IN ( SELECT jm.id FROM jira_mini jm WHERE (jm.issue_from LIKE 'APEX500' OR (jm.issue_from NOT LIKE 'APEX500' AND jm.assigned_by IS NOT NULL)) AND jm.created_date >= TO_DATE('2016-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS') ) ) t3 ON j.id = t3.jira_mini_id LEFT JOIN sources.series_dataset_v ssv ON ssv.id = j.object_id LEFT JOIN sources.outage_dataset_v odv ON odv.id = j.object_id LEFT JOIN safe_user su1 ON su1.id = j.created_by LEFT JOIN safe_user su2 ON su2.id = j.assigned_by ORDER BY j.id, t3.created_date ASC;