user3753620 user3753620 - 4 years ago 126
MySQL Question

How to find results that don't contain results on the same day

I am trying to figure out how to get a set of results returned to me in SQL.

I have a column called "Status" that has either 'Passed' or 'Failed' as a value. Within the same table is a column for "Date_Entered"

I need a query to return all of the days that 'Failed' that do not have a 'Passed' for the same day.


01/01/2017 - Failed
01/01/2017 - Failed
01/01/2017 - Failed
01/01/2017 - Passed

01/02/2017 - Failed
01/02/2017 - Failed

01/03/2017 - Passed

In my result set, I only want to see both entries on 01/02/2017 since the 1st and the 3rd has a 'Passed' result.

Here is my most recent attempt:

select COUNT(*)
, CAST(h.Status AS VARCHAR) status
, CAST(h.Location AS VARCHAR) location
, CAST(h.Date_Entered AS DATE)

from de_Hgb h
where 1=1
and h.Date_Entered between '01/01/2017' and '01/31/2017 23:59:59'
and h.status not in (SELECT CAST(g.Status AS VARCHAR) status FROM de_Hgb g WHERE 1=1 and g.Status <> 'Passed' AND g.Date_Entered = h.Date_Entered)
GROUP BY h.date_entered, h.Status, h.location

However, this is returning the inverse of what I want; this is showing me the entries for the 1st and the 3rd, not the 2nd.

Answer Source

Here's one way - select all the failed dates, then left join against the same table on those dates with a 'Passed' status. Then you just need rows where that is NULL (i.e. there was no Passed status)...

SELECT DISTINCT failed.Date_Entered 
FROM de_Hgb AS failed 
LEFT JOIN  de_Hgb AS passed 
  ON(passed.Date_Entered=failed.Date_Entered and passed.Status="Passed")
WHERE failed.Status="Failed" AND passed.Status IS NULL;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download