Case statement with partition

Question

I have to update status for each ID according to the conditions below. One ID can have multiple status but I have to pick status based on the conditions.

My sample schema is like:

CREATE TABLE [dbo].[Table1](
    [ID] [nvarchar](50) NULL,
    [ApprovalDate] [datetime] NULL,
    [CreateDate] [datetime] NULL,   
    [status] [nvarchar](50) NULL
) 
;
Insert into Table1 values (1,null,null,'Approved')
Insert into Table1 values (1,null,null,'Modified')
Insert into Table1 values (1,null,null,'Later')
Insert into Table1 values (2,null,null,'Approved')
Insert into Table1 values (2,null,null,'Modified')
Insert into Table1 values (2,null,null,'Approved')
Insert into Table1 values (3,null,null,'Modified')
Insert into Table1 values (3,null,null,'Later')
Insert into Table1 values (4,null,null,'Approved')

If ID 1 has Approved, Later,Modified I should say "Partially Modified" and if the ID has approved and Modified then also "Partially Modified".But the case statement I got is not doing per ID.It is changing the overall data based on status.Please advise. I have included SQL fiddler, Output should have ID 1 - Partially Modified, 2-Partially Modified,3-Modified,4-Approved .. with same number of rows as in table ..

select ID,
   CASE
      WHEN status = 'Approved' AND status IN('Modified','Later') 
         THEN 'Partial Modified'
      WHEN status = 'Approved' 
         THEN 'Approved'
      WHEN status IN('Modified','Edited') THEN 'Modified'
      ELSE status
      END status 
 group by ID,Status

http://sqlfiddle.com/#!18/9bf47/7/0

Answer 1

Below can be a possible solution.

SELECT ID,
   CASE WHEN MAX( CASE WHEN [status] = 'Approved' THEN 'Approved' ELSE NULL END ) = 'Approved'
            AND MAX( CASE WHEN [status] IN('Modified','Later') THEN 'Partial Modified' ELSE NULL END ) = 'Partial Modified'
        THEN 'Partial Modified'
        ELSE MAX([status]) -- this can go wrong if there are multiple status which don't fall into above condition.
        END AS [Status]     
FROM Table1
GROUP BY ID

Method:2 If you want result for same number of rows as in table use below query.

;WITH CTE AS (
    SELECT ID,
       CASE WHEN MAX( CASE WHEN [status] = 'Approved' THEN 'Approved' ELSE NULL END ) = 'Approved'
                AND MAX( CASE WHEN [status] IN('Modified','Later') THEN 'Partial Modified' ELSE NULL END ) = 'Partial Modified'
            THEN 'Partial Modified'
            WHEN MAX( CASE WHEN [status] = 'Modified' THEN 'Modified' ELSE NULL END ) = 'Modified'
                AND MAX( CASE WHEN [status] IN('Edited','Later') THEN 'Modified' ELSE NULL END ) = 'Modified'
            THEN 'Modified'
            ELSE NULL -- this can go wrong if there are multiple status which don't fall into above condition.
            END AS [Status]     
    FROM Table1
    GROUP BY ID
) 
SELECT 
    t.ID,
    ISNULL(c.[status],t.[status]) AS [status]
FROM CTE AS c
INNER JOIN Table1 AS t ON t.ID = c.ID
GROUP BY t.ID, t.[status],c.[status]

OUTPUT

ID      status
------- ----------------
1       Partial Modified
1       Partial Modified
1       Partial Modified
2       Partial Modified
2       Partial Modified
3       Modified
3       Modified
4       Approved