Need to get 'Yes if source list include both type of values 'NA' and other values, re-CodePudding

One of request for my code is to set value for Policy Exception 'Yes', 'No' and 'Null' for next 4 scenarios:

a. Source table can have next CODE:
'AAR',
'DSC',
'FCCR',
'NA' -> In this case I need to set value 'YES';

c. Source list can have values without 'NA' :
'AAR',
'DSC',
'FCCR'-> In this case I need to set also value 'YES'

b. Source table can have just value 'NA' -> In this case I need to set value 'NO'

c. Source table can be empty -> In this case I need to set value 'NULL'

Could you please help me to create the code. Its challenging for me step a and b. I used COUNT(DISTINCT CODE) but something went wrong.

Thank you!

CodePudding user response：

This is how I understood it - conditional aggregation.

Sample data:

SQL> with test (app_id, policyexceptions) as
  2    (select 57, 'AAR'  from dual union all
  3     select 57, 'DSC'  from dual union all
  4     select 57, 'FCCR' from dual union all
  5     select 57, 'NA'   from dual union all
  6     --
  7     select 58, 'AAR'  from dual union all
  8     select 58, 'DSC'  from dual union all
  9     select 58, 'FCCR' from dual union all
 10     select 58, 'AMO'  from dual union all
 11     --
 12     select 59, 'NA'   from dual union all
 13     select 59, 'NA'   from dual union all
 14     select 59, null   from dual union all
 15     select 59, null   from dual union all
 16     --
 17     select 50, null   from dual union all
 18     select 50, null   from dual union all
 19     select 50, null   from dual union all
 20     select 50, null   from dual
 21    ),

Query begins here; lines #37-40 have (as a comment) ordinal number of conditions you specified. I didn't use "a, b, c" because you have two "c" options, so ... I doubt it is correct, one of them should be "d"

 22  temp as
 23    (select app_id,
 24       sum(case when policyexceptions = 'NA'  then 1
 25                when policyexceptions is null then null
 26                else 0 end) sum_na,
 27       sum(case when policyexceptions = 'NA'  then 0
 28                when policyexceptions is null then null
 29                else 1 end) sum_not_na,
 30       sum(case when policyexceptions is null then 1 else 0 end) sum_null,
 31       count(*) cnt
 32     from test
 33     group by app_id
 34    )
 35  select app_id,
 36    --
 37    case when sum_null = cnt then 'Null'                -- 4)
 38         when sum_na > 0 and sum_not_na > 0 then 'Yes'  -- 1)
 39         when sum_na = 0 and sum_not_na > 0 then 'Yes'  -- 2)
 40         when sum_na   sum_null = cnt       then 'No'   -- 3)
 41    end as status
 42  from temp
 43  order by app_id;

    APP_ID STATUS
---------- ----------
        50 Null
        57 Yes
        58 Yes
        59 No

SQL>

CodePudding user response：

Create a common table expression that summarises how many of each type of policy exceptions there are for each app_id, then use a case to chose the final result:

with summary as (
  select
    app_id,
    sum(case when policyexceptions = 'NA' then 1 else 0 end) as na_cnt,
    sum(case when policyexceptions != 'NA' then 1 else 0 end) as other_cnt
  from mytable
  group by app_id
)
select
  app_id,
  case
    when other_cnt > 0 then 'YES'
    when other_cnt = 0 and na_cnt > 0 then 'NO'
    else null
  end as policyexception
from summary

See live demo.