SQL Conditional Case

Question

I have a table that lists items and a status about these items. The problem is that some items have multiple different status entries. For example.

HOST          Status
1.1.1.1       PASS
1.1.1.1       FAIL
1.2.2.2       FAIL
1.2.3.3       PASS
1.4.2.1       FAIL
1.4.2.1       FAIL
1.1.4.4       NULL

I need to return one status per asset.

HOST          Status
1.1.1.1       PASS
1.2.2.2       FAIL
1.2.3.3       PASS
1.4.2.1       FAIL
1.1.4.4         No Results

I have been trying to do this with T-SQL Case statements but can't quite get it right. The conditions are any Pass + anything is a Pass, Fail+ No Results is a fail and Null is No Results.

So what are the rules, why is the pass for 1.1.1.1 desired instead of the fail. Please don't say's the first unless you have some other column that indicates what order they are in. — Tony Hopkinson, Jan 06 '14 at 20:58
With a status date, and a rule use the latest, you wouldn't need case at all for intance. — Tony Hopkinson, Jan 06 '14 at 21:00
I would store status as something numeric (like 1 or 0 for pass/fail) and then do a group by on the host and sum the status and then do your case logic on that sum... — Rikon, Jan 06 '14 at 21:00
This is an authentication report from a system that attempts different authentication methods regardless of OS. For 1.1.1.1 Windows Auth passed but Unix failed. @TonyHopkinson — Eddie D, Jan 06 '14 at 21:04

score 4 · Accepted Answer · answered Jan 06 '14 at 21:05

Try using a case statement to convert to ordered results and group on that, finally, you'll need to convert back to the nice, human-readable answer:

with cte1 as (
  SELECT HOST,
         [statNum] = case
                      when Status like 'PASS' then 2
                      when Status like 'FAIL' then 1
                      else 0
                    end
  FROM table
)
SELECT HOST, case max(statNum) when 2 then 'PASS' when 1 then 'FAIL' else 'No Results' end
FROM cte1
GROUP BY HOST

NOTE: I used a CTE statement to hopefully make things a little clearer, but everything could be done in a single SELECT, like so:

SELECT HOST,
 [Status] = case max(case when Status like 'PASS' then 2 when Status like 'FAIL' then 1 else 0 end)
    when 2 then 'PASS'
    when 1 then 'FAIL'
    else 'No Result'
   end
FROM table

With this same query is it possible to nest the CAST statements. I have an "exclusions" table that I need to also factor in. The first cast would be to set the status as Exclusion for matching hosts then this query for the hosts that do not match the exclusion — Eddie D, Jan 07 '14 at 23:01
@EddieD, I'm not sure, exactly, what you're trying to do, but in general you can nest `cast` statements (and `case` statements, if that's what you meant). So, give it a shot and if you run into another wall putting everything together, you can always ask another question! — chezy525, Jan 08 '14 at 15:43

Kaf · Answer 2 · 2014-01-06T21:27:22.697

2

You can use Max(Status) with Group by Host to get Distinct values:

Select host, coalesce(Max(status),'No results') status
From Table1
Group by host
Order by host

Fiddle Demo Results:

|    HOST |     STATUS |
|---------|------------|
| 1.1.1.1 |       PASS |
| 1.1.4.4 | No results |
| 1.2.2.2 |       FAIL |
| 1.2.3.3 |       PASS |
| 1.4.2.1 |       FAIL |

By default SQL Server is case insensitive, If case sensitivity is a concern for your server, then use the lower() function as below:

Select host, coalesce(Max(Lower(status)),'No results') status
From Table1
Group by host
Order by host

Fiddle demo

edited Jan 06 '14 at 21:27

answered Jan 06 '14 at 21:05

Kaf

33,101
7
58
78

Be careful with this! Even though `"FAIL" < "PASS"`, often `"PASS" < "fail"` (note the case of the characters) – chezy525 Jan 06 '14 at 21:15
@chezy525: By default, SQL Server is case insensitive. Case sensitivity can also be handled using `Lower()` and `Upper()` functions. Usage would be like `coalesce(Max(Lower(status)),'No results')`. – Kaf Jan 06 '14 at 21:19
alternatively, you could force a collation in the aggregate function: `max(Status COLLATE Latin1_General_CI_AI)`... I don't know which is more efficient... – chezy525 Jan 06 '14 at 21:34

score 0 · Answer 3 · answered Jan 06 '14 at 21:09

WITH CTE( HOST, STATUSValue)
AS(
     SELECT HOST,
     CASE STATUS WHEN 'PASS' 1 ELSE 0 END AS StatusValue
     FROM Data
)

SELECT DISTINCT HOST,
  CASE ISNULL(GOOD.STATUSVALUE,-1) WHEN 1 THEN 'Pass' 
       ELSE CASE ISNULL( BAD.STATUSVALUE,-1) WHEN 0 Then 'Fail' Else 'No Results' END
  END AS Results
FROM DATA AS D
LEFT JOIN CTE AS GOOD
  ON GOOD.HOST = D.HOST
 AND GOOD.STATUSVALUE = 1
LEFT JOIN CTE AS BAD
  ON BAD.HOST = BAD.HOST
 AND BAD.STATUSVALUE = 0

SQL Conditional Case

3 Answers3