OTBI analytical function to get Yes or no for an employee according to another column value

user_1490556 0
Viewed 73

My OTBI(Oracle Transactional Bi report) report have the following columns -

 Person number  Date_to        comp               profile_updated
      10          10-jan-2021     Specialization             Y
      10                                                     N
      10          01-jun-2022     Cert                       Y
      
      11         01-jan-2022      Qualification              N
      11                          Cert                       N

I want to add a new column Yes or No at the end, which will have value Yes, if any of the values in profile_updated column is Y.

Eg- For person no. 10 it should be Y, for 11 it should be N

Person number  Date_to        comp               profile_updated           Yes/No
      10          10-jan-2021     Specialization             Y               Y
      10                                                     N               y
      10          01-jun-2022     Cert                       Y               y   
      
      11         01-jan-2022      Qualification              N               n
      11                          Cert                       N               n

Is there an analytical function i can use for this ?

oracle-bi oracle-sqldeveloper sql
1 Answers

You may try using a join approach here:

SELECT t1.*, CASE WHEN t2.cnt > 0 THEN 'Y' ELSE 'N' END AS "Yes/No"
FROM yourTable t1
INNER JOIN
(
    SELECT "Person number",
           COUNT(CASE WHEN profile_updated = 'Y' THEN 1 END) AS cnt
    FROM yourTable
    GROUP BY "Person number"
) t2
    ON t2."Person number" = t1."Person number";

You might also be able to use COUNT() as an analytic function here and avoid the join:

SELECT t.*,
       CASE WHEN COUNT(CASE WHEN profile_updated = 'Y' THEN 1 END)
                 OVER (PARTITION BY "Person number") > 0
            THEN 'Y' ELSE 'N' END AS "Yes/No"
FROM yourTable t
ORDER BY "Person number";
user_18632290
Related