Identify rows with particular value across multiple columns

Each row has an Id and a Category Presence/Status combo. Sample code:

DECLARE @sample_table TABLE (
    Id INT
    ,Category_Alpha BIT
    ,Category_Beta BIT
    ,Category_Charlie BIT
    ,Status_Alpha CHAR(8)
    ,Status_Beta CHAR(8)
    ,Status_Charlie CHAR(8)
    );
INSERT INTO @sample_table
VALUES
    (1,1,0,0,'Approved','Open','Open')
    ,(2,1,1,0,'Pending','Approved','Open')
    ,(3,0,0,1,'Open','Open','Rejected')
    ,(4,0,1,0,'Open','Approved','Open')
    ,(5,1,1,1,'Approved','Rejected','Approved');
SELECT * FROM @sample_table;

Produces:

Id  Category_Alpha  Category_Beta   Category_Charlie    Status_Alpha    Status_Beta Status_Charlie
1        1               0               0                  Approved    Open        Open    
2        1               1               0                  Pending     Approved    Open    
3        0               0               1                  Open        Open        Rejected
4        0               1               0                  Open        Approved    Open    
5        1               1               1                  Approved    Rejected    Approved

So each row can have an “Alpha” (true/false) and a corresponding Status for “Alpha” (Pending, Approved, etc.). The same for the other categories (Beta and Charlie). Categories that are vacant always have a status of “Open”. For example, row with Id=1 has Category_Beta=0 (False), so corresponding Status_Beta is “Open”.

I have provided 3 categories in the example (Alpha, Beta, Charlie), but my actual data has over 40 categories. I want to find rows where ALL non-vacant categories are “Approved”. In the provided example, this would be rows 1 and 4.

Answer

This would just be a complicated where clause:

select t.*
from t
where (category_alpha = 0 or status_alpha = 'Approved') and
      (category_beta = 0 or status_beta = 'Approved') and
      (category_charlie = 0 or status_charlie = 'Approved');

Advertisement

Answer