All Forums Database
abhinavtd 3 posts Joined 04/15
16 Apr 2015
Windows Aggregate Function

Hi everyone,

I was hoping that some one could help me create a SQL to deduce the following result.

We are trying to roll up multiple roles into a single role.

 

Below is the example of the roles currently defined for the two users -

 

Existing Role                        User_ID                         User_Name

-------------                            -------                         ---------

R_PROD_WIL_CUST              narh1                          Aaron Roach

R_PROD_WIL                        narh1                          Aaron Roach

R_PROD_WIL_MART              narh1                          Aaron Roach

R_PROD_WIL                        nalu8                           Abigail Liu

R_PROD_APP_WIL_USER      nalu8                           Abigail Liu

R_PROD_WIL_CUST              nalu8                           Abigail Liu

R_PROD_SALES_ANLYTCS     nalu8                           Abigail Liu

 

Any user which has the 3 roles - R_PROD_WIL + R_PROD_WIL_CUST + R_PROD_WIL_MART - will be given a new role as R_PROD_CUSTOMER_ADVN_ANALYTICS.

Any user which has the role R_PROD_SALES_ANLYTCS will continue to have same role.

Any user which doesnt have all the 3 roles - R_PROD_WIL + R_PROD_WIL_CUST + R_PROD_WIL_MART - will be assigned UnMapped_Role.

So the output should like - 

 

Existing Role                         New Role                                                        User_ID                         User_Name

-------------                            --------                                                            -------                             -----------

R_PROD_WIL_CUST            R_PROD_CUSTOMER_ADVN_ANALYTICS              narh1                           Aaron Roach

R_PROD_WIL                      R_PROD_CUSTOMER_ADVN_ANALYTICS              narh1                           Aaron Roach

R_PROD_WIL_MART            R_PROD_CUSTOMER_ADVN_ANALYTICS              narh1                           Aaron Roach

R_PROD_WIL                      UnMapped_Role                                                   nalu8                           Abigail Liu

R_PROD_APP_WIL_USER    UnMapped_Role                                                   nalu8                           Abigail Liu

R_PROD_WIL_CUST            UnMapped_Role                                                   nalu8                           Abigail Liu

R_PROD_SALES_ANLYTCS   R_PROD_SALES_ANLYTCS                                   nalu8                           Abigail Liu

 

Assume these columns are coming from the same table.

We need both the existing role and the new role so that we are able to report how the existing role maps to the new role.

 

Best Regards,

Abhinav

 

dnoeth 4628 posts Joined 11/04
30 Apr 2015

Hi Abhinav,
this should return the expected result:

SELECT .....
   CASE
     WHEN ExistingRole = 'R_PROD_SALES_ANLYTCS' THEN ExistingRole
     WHEN ExistingRole IN ('R_PROD_WIL','R_PROD_WIL_CUST','R_PROD_WIL_MART') -- a row with one of those roles
      AND MAX(CASE WHEN ExistingRole = 'R_PROD_WIL'      THEN 1 ELSE 0 end) OVER (PARTITION BY User_ID)
        + MAX(CASE WHEN ExistingRole = 'R_PROD_WIL_CUST' THEN 1 ELSE 0 end) OVER (PARTITION BY User_ID)
        + MAX(CASE WHEN ExistingRole = 'R_PROD_WIL_MART' THEN 1 ELSE 0 end) OVER (PARTITION BY User_ID) = 3  -- all three roles must exist for this user
     THEN 'R_PROD_CUSTOMER_ADVN_ANALYTICS'
     ELSE 'UnMapped_Role'
   END
FROM tab 

 

Dieter

You must sign in to leave a comment.