CODEWITHSUNDEEP
sql-servercaserow-numberdatabase-partitioning
user1046415
user1046415

Reputation: 789

Case statement with partition

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

Upvotes: 1

Views: 344

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17126

you can use query like below. Note that this approach allows you pre-map all uses cases as simple combination of integer values rather than complicated case expressions like WHEN status = 'Approved' AND status IN('Modified','Later')

See live demo

select id,
case sum(v) 
    when 1 then 'approved'
    when 2 then 'modified'
    when 3 then 'partially modified'
    when 4 then 'partially modified'
    when 5 then 'partially modified'
    when 6 then 'modified'
    when 7 then 'partially modified'
end [status] from
Table1 t1 join
 (values( 'approved',1),('Modified',2),('later',4)) as t(s,v)
 on t1.[status]=t.s
 group by id

want the ID row to repeat with the same final status

so in that case solution will be

select 
T1.*,
T.[status]

from
Table1 T1
Join
(
    select id,
    case sum(v) 
        when 1 then 'approved'
        when 2 then 'modified'
        when 3 then 'partially modified'
        when 4 then 'partially modified'
        when 5 then 'partially modified'
        when 6 then 'modified'
        when 7 then 'partially modified'
    end [status] from
    Table1 t1 join
     (values( 'approved',1),('Modified',2),('later',4)) as t(s,v)
     on t1.[status]=t.s
     group by id
)T
on T.id=T1.id

Upvotes: 0

Jatin Patel
Jatin Patel

Reputation: 2104

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

Upvotes: 2

Related Questions