SQL Server - Case

user_2167668 0
Viewed 104
CREATE TABLE test
(
sts_id int 
, [status1] int
, [status2] int
, [status3] int
, [status4] int
)
INSERT INTO test values
('1','999','0','0','0'),
('1','100','0','0','0'),
('2','200','999','0','0'),
('3','500','600','999','0'),
('4','200','700','900','998'),
('4','300','400','800','999')

In SQL Server (2016), I have a query for the above table to get the highest value <= 900 (if possible) 

SELECT DISTINCT 
   sts_id
 , CASE
       WHEN status1 > 0 AND status2 = 0 THEN status1
       WHEN status2 > 900 AND status3 = 0 THEN status1
       WHEN status2 BETWEEN 1 AND 900 AND status3 = 0 THEN status2
       WHEN status3 > 900 AND status4 = 0 THEN status2
       WHEN status3 BETWEEN 1 AND 900 AND status4 = 0 THEN status3
       WHEN status4 > 900 THEN status3 WHEN status4 BETWEEN 1 AND 900 THEN status4
       ELSE status1
   END AS 'status'
FROM test

Giving me this:

| sts_ID | status |
|--------|--------|
|  1     |  100   |
|  1     |  999   |
|  2     |  200   |
|  3     |  600   |
|  4     |  800   |
|  4     |  900   |

I then join this onto another table on the sts_id, and get the max(status) value.

However, I am not concerned with statuses above 900, and sts_id 1 for example is going to give me 999, even though I would prefer it to give me 100.

Is there any way I can incorporate some kind of CASE to get the next value if there is one available for a single ID? Would dense_rank() possibly help?

case sql-server sql
4 Answers

Seems like this would be easier if you unpivot your table into a single status column. Here is a method with CROSS APPLY:

SELECT sts_id, MAX(status) MaxStatus
FROM TEST
CROSS APPLY (VALUES (status1), 
                    (status2), 
                    (status3), 
                    (status4)
            ) A (status)
WHERE STATUS <= 900
GROUP BY sts_id

Result:

STS_ID  MaxStatus
1       100
2       200
3       600
4       900
user_54200080

I would use apply and max(), but like this:

SELECT sts_id, v.MaxStatus
FROM TEST CROSS APPLY
     (SELECT MAX(status) as MaxStatus
      FROM (VALUES (status1), 
                   (status2), 
                   (status3), 
                   (status4)
           ) v(status)
      WHERE STATUS <= 900
     ) v;

This may look essentially the same as Aaron's answer. However, there is a fundamental difference. The aggregation is happening within a row, with at most 4 values. This should be very fast. Putting the aggregation outside the apply means that the entire data needs to be aggregated -- and is a bit more cumbersome as you want to add columns.

In addition, this will return all rows, even when no statuses meet those conditions. If you want a row to be filtered in this case, then add where maxstatus is not null to the outer query.

user_11440350

Something like this, maybe? (Referencing your final table output as inline_view): 

        SELECT sts_id, MAX(status)
        FROM
        (
        SELECT sts_id, status, CASE WHEN status > 900 THEN 0 ELSE 1 END AS status_above_900
        FROM inline_view
        ) a1
        WHERE status_above_900 = 1
        GROUP BY sts_id
user_89598070

The following statement gives you the highest status form status4 to status1, that is equal to or below to 900.

SELECT sts_id,
       CASE
         WHEN status4 > 0
              AND status4 <= 900 THEN
           status4
         WHEN status3 > 0
              AND status3 <= 900 THEN
           status3
         WHEN status2 > 0
              AND status2 <= 900 THEN
           status2
         WHEN status1 > 0
              AND status1 <= 900 THEN
           status1
       END status
       FROM test;

Result:

sts_id | status
-----: | -----:
     1 |
     1 |    100
     2 |    200
     3 |    600
     4 |    900
     4 |    800

For one of the sts_id = 1 there is no such status, so NULL is returned for that.

But you say, you want 100 for sts_id = 1. So, I'm not sure if you rather want an aggregation and only one row per sts_id.

If you do, one possible way was to first get all the status per sts_id with a UNION (it's fine to eliminate duplicates here as we do that with the GROUP BY later anyway), filtered for values less than or equal to 900 and then GROUP BY sts_id and get the maximum value per sts_id.

SELECT sts_id,
       max(status) status
       FROM (SELECT sts_id,
                    status1 status
                    FROM test
                    WHERE status1 <= 900
             UNION
             SELECT sts_id,
                    status2 status
                    FROM test
                    WHERE status2 <= 900
             UNION
             SELECT sts_id,
                    status3 status
                    FROM test
                    WHERE status3 <= 900
             UNION
             SELECT sts_id,
                    status4 status
                    FROM test
                    WHERE status4 <= 900) x
       GROUP BY sts_id;

Result:

sts_id | status
-----: | -----:
     1 |    100
     2 |    200
     3 |    600
     4 |    900
user_96614240
Related