I have a table that looks something like this, Report_Table:

I wanted to pull data from Report_table. If COMPLETION_STATUS is Success it should give the latest Success COMPLETION_DATE table date and if it has Error it should give the last Success COMPLETION_DATE table date as well as the Error date. Something like

select Report_DEFINITION_NAME, Report_Core_name, COMPLETION_STATUS, COMPLETION_DATE,

CASE: WHEN COMPLETION_STATUS='Success' THEN latest Success COMPLETION_DATE WHEN COMPLETION_STATUS='Error' THEN last Success COMPLETION_DATE WHEN COMPLETION_STATUS='Error' THEN latest Error COMPLETION_DATE END

from Report_Table;

the output should be in a single query identified by core name or definition name.

CodePudding user response：

So, I have used IN clause to solve my problem and no Case expression.

Getting the max value in a select statement and calling that statement using IN clause from my parent select statement solved my problem. something like:

SELECT 
  a.*,
  a.completion_date,
  b.completion_date AS last_success_date
FROM report_table a, report_table b
WHERE a.report_definition_name = b.report_definition_name
AND a.completion_date IN 
(
  SELECT MAX(su.completion_date) 
  FROM report_table su 
  WHERE su.completion_status = 'Success'
  AND b.report_definition_name = su.report_definition_name
)
ORDER BY a.completion_date DESC;