Windows Aggregate Function

Database
Teradata Employee

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

1 REPLY
Junior Contributor

Re: Windows Aggregate Function

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