Help on case statement

General
Enthusiast

Help on case statement

Hi
Good Day!
 
Can you please help me on the below scenario
 
I have  a table with Two columns namely WO_Key , WO_DSC. I need to derive the flag based on the following logic
 
Input Table details:
--------------------------
WO_Key                    WO_DSC
--------------              --------------------
100                          PT
100                         NPT
100                         RC
100                         N/A
100                         SC
100                       UNKNWN
200                       NPT
200                       NPT
300                       PT
300                       RC
300                       NPT
400                     UNKNWN
400                     RC
400                     N/A
 
Expected Output :
 
WO_Key                    WO_DSC                   Flag
--------------              -------------------             --------------
100                          PT                                 SC       
100                         NPT                                SC
100                         RC                                   SC
100                         N/A                                   SC
100                         SC                                   SC
100                       UNKNWN                         SC
 200                       NPT                                  NPT
200                       NPT                                  NPT
 300                       PT                                     PT
300                       RC                                     PT
300                       NPT                                   PT
 400                     UNKNWN                              ?
400                     RCM                                      ?
400                     N/A                                        ?
 
logic to derive the FLAG:
1)For the set of same WO_KEY, If WO_DSC has atleast one record with the value 'SC' then FLAG=SC
2) for the set of same WO_KEY ,If WO_DSC Has the value of NPT for all the reacord then FLAG=NPT
3)For the set of same WO_KEY , if WO_DSC has atlease one PT value and none other records has 'SC' then FLAG=PT
4) For all other scenarios,Wecan populate FLAG as '?'
 
Note: WO_DSC has the below possible values
  PT
NPT
RC
N/A
SC
UNKNWN
 
 
Thanks in adavce !
Tags (2)

Accepted Solutions
Junior Contributor

Re: Help on case statement

You need conditional aggregation:

CASE
   --  If WO_DSC has at least one record with the value 'SC' then FLAG=SC
   WHEN Max(CASE WHEN WO_DSC = 'SC'  THEN 1 ELSE 0 end) Over (PARTITION BY WO_KEY) = 1 THEN 'SC'
   -- If WO_DSC Has the value of NPT for all the records then FLAG=NPT
   WHEN Max(CASE WHEN WO_DSC = 'NPT' THEN 0 ELSE 1 end) Over (PARTITION BY WO_KEY) = 0 THEN 'NPT'
   --  if WO_DSC has at least one PT value and none other records has 'SC' then FLAG=PT (covered by 1st WHEN)
   WHEN Max(CASE WHEN WO_DSC = 'PT'  THEN 1 ELSE 0 end) Over (PARTITION BY WO_KEY) = 1 THEN 'PT'
   ELSE '?'
END
1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Help on case statement

You need conditional aggregation:

CASE
   --  If WO_DSC has at least one record with the value 'SC' then FLAG=SC
   WHEN Max(CASE WHEN WO_DSC = 'SC'  THEN 1 ELSE 0 end) Over (PARTITION BY WO_KEY) = 1 THEN 'SC'
   -- If WO_DSC Has the value of NPT for all the records then FLAG=NPT
   WHEN Max(CASE WHEN WO_DSC = 'NPT' THEN 0 ELSE 1 end) Over (PARTITION BY WO_KEY) = 0 THEN 'NPT'
   --  if WO_DSC has at least one PT value and none other records has 'SC' then FLAG=PT (covered by 1st WHEN)
   WHEN Max(CASE WHEN WO_DSC = 'PT'  THEN 1 ELSE 0 end) Over (PARTITION BY WO_KEY) = 1 THEN 'PT'
   ELSE '?'
END
Enthusiast

Re: Help on case statement

Many Thanks for your timely help.