SQL Query to get flag as Y for specific column values of a column

Question

I have a query that is fetching data as follows between two dates. The query is already fetching data as follows. Since the query has lot of calculations & is long I am just writing the final table output I am getting -

person_number   OVT     Hour3_code          hour3_amount            
10                          UNP                 7                   
10                          PUB                 8                   
10                          STR                 16                                  

8                           unp                 7                   
8                           PUB                 8

16                          UNP                 8

17              10          vac                 9
17                          STR                 8

15                          UNP                 6.5
15                          STH                 5

I want to create a query on top of this table such that if for a person number there is only "UNP" ,"STH" AND PUB in hour3_code, then cancel flag (added column) should be Y, If there are more values in HOUR3_CODE like -STR or there is value for OVT column then it should be N.

So expected output is -

person_number   OVT     Hour3_code          hour3_amount        CANCEL          
10                          UNP                 7                   N                   
10                          PUB                 8                   
10                          STR                 16                                  

8                           unp                 7                   Y                   
8                           PUB                 8

16                          UNP                 8                   Y

17              10          vac                 9                   N
17                          STR                 8

15                          UNP                 6.5                 Y
15                          STH                 5

So, since 8 has just UNP, PUB it has cancel_pay Y , Since 15 has only UNP STH it has Y , since 16 has UNP it has Y. How to achieve this ?

Answer 1

Here's one option. Read comments within code.

Sample data:

SQL> with person (person, ovt, hour3) as
  2    (select 10, null, 'unp' from dual union all
  3     select 10, null, 'pub' from dual union all
  4     select 10, null, 'str' from dual union all
  5     --
  6     select  8, null, 'unp' from dual union all
  7     select  8, null, 'pub' from dual union all
  8     --
  9     select 16, null, 'unp' from dual union all
 10     --
 11     select 17,   10, 'vac' from dual union all
 12     select 17, null, 'str' from dual union all
 13     --
 14     select 15, null, 'unp' from dual union all
 15     select 15, null, 'sth' from dual
 16    ),

Query begins here:

 17  fails as
 18    -- which PERSON rows have a wrong value in HOUR3 (or have
 19    -- something in OVT) column?
 20    (select distinct a.person
 21     from person a
 22     where exists (select null
 23                   from person b
 24                   where b.person = a.person
 25                     and (   b.hour3 not in ('unp', 'sth', 'pub')
 26                          or b.ovt is not null
 27                         )
 28                  )
 29    )
 30  -- CANCEL is "N" if PERSON is found in FAILS CTE; "Y" otherwise
 31  select p.person, p.ovt, p.hour3,
 32    case when f.person is not null then 'N' else 'Y' end as cancel
 33  from person p left join fails f on f.person = p.person
 34  order by p.person, p.hour3;

    PERSON        OVT HOUR3 CANCEL
---------- ---------- ----- ------
         8            pub   Y
         8            unp   Y
        10            pub   N
        10            str   N
        10            unp   N
        15            sth   Y
        15            unp   Y
        16            unp   Y
        17            str   N
        17         10 vac   N

10 rows selected.

SQL>