Naresh Naresh - 4 months ago 8
SQL Question

Oracle SQL Query to find records having particular status having maximum date

I need to write Oracle sql query to get the output like below.Both Start and End Date are DATE data type.I have a file like this



ApName DbName Status StartDate EndDate
A1 A11 Success 8/3/2016 8/3/2016
A1 A11 Failed 8/3/2016 8/3/2016
A2 A22 Success 8/2/2016 8/2/2016
A3 A33 Success 8/2/2016 8/2/2016
A4 A44 Failed 8/2/2016 8/2/2016
A4 A44 Failed 8/2/2016 8/2/2016
A4 A44 Success 8/3/2016 8/3/2016
A5 A55 Failed 8/3/2016 8/3/2016
A5 A55 Failed 8/3/2016 8/3/2016
A2 A22 Success 8/3/2016 8/3/2016


I need output where apname and dbname have success and fail or only failed status.I dont want apname and dbname where the status is success only. All this condition shouldbe applied for maximum date(e.g., 8/3/2016 based on the input file)


ApName DbName Status StartDate EndDate
A1 A11 Success 8/3/2016 8/3/2016
A1 A11 Failed 8/3/2016 8/3/2016
A5 A55 Failed 8/3/2016 8/3/2016
A5 A55 Failed 8/3/2016 8/3/2016

Answer

Assuming Status is either 'Success' or 'Failed', it should do the job :

SELECT * FROM 
(
    SELECT a.* , 
    count(distinct Status) over (partition by a.AppName, trunc(a.EndDate)) as  statusCnt, 
    max(endDate) over(partition by a.AppName) as lastDate
    FROM your_table a
)b
WHERE trunc(b.lastDate) = trunc(b.EndDate) AND (statusCnt > 1 OR Status = 'Failed');

I may misinterpret the part regarding "maximum date" though. My query compares maximum date for each AppName, not overall maximum date.

Comments