CODEWITHSUNDEEP
sql-servercase
bojackh
bojackh

Reputation: 97

Case statement based upon multiple column values

I'd like to either add a case statement to return Y/N for the 'All Complete?' column or exclude all rows where 'All complete' = 'Y'.

In order for 'All Complete' to be set to Y, all ID_Status's need to be complete for each customer and each code number. Each customer can have multiple ID's or Code_Number. I'm having trouble understanding CASE logic across multiple columns where values aren't always the same. Thanks for any help.

IDTable:
ID   ID_Status Customer  Code_Number  All Complete?

1    Complete  Alex      123          Y
2    Complete  Alex      123          Y
3    Complete  Brian     321          Y
4    Complete  Brian     321          Y
5    Open      Brian     425          N
6    Complete  Brian     425          N
7    Open      Charlie   123          N
8    Complete  Charlie   123          N
9    Open      Charlie   123          N
10   Complete  Donald    555          N
11   Complete  Donald    555          N
12   Testing   Donald    555          N
13   Complete  Eric      620          Y

Upvotes: 0

Views: 161

Answers (2)

M.Ali
M.Ali

Reputation: 69504

Select * 
   , CASE WHEN EXISTS 
                     (SELECT 1 FROM TableName t1
                       Where t1.Customer = t2.Customer
                         AND t1.Code_Number = t2.Code_Number
                         AND t1.ID_Status <> 'Complete'
                      )
          THEN 'N' ELSE 'Y' END AS [All Complete?]

from TableName t2

Upvotes: 0

Lamak
Lamak

Reputation: 70638

Assuming SQL Server 2008+:

WITH CTE AS
(
    SELECT  *,
            COUNT(*) OVER(PARTITION BY Customer, Code_Number) Total,
            SUM(CASE WHEN Id_Status = 'Complete' THEN 1 ELSE 0 END) OVER(PARTITION BY Customer, Code_Number) Completed
    FROM dbo.YourTable
)
SELECT  ID,
        ID_Status,
        Customer,
        Code_Number,
        CASE WHEN Total = Completed THEN 'Y' ELSE 'N' END [All Complete]
FROM CTE;

Upvotes: 2

Related Questions