CODEWITHSUNDEEP
sqloracle-database
User2321
User2321

Reputation: 3062

Filtered comparison in PLSQL

I have a table with the following format:

ID,FLAG,PERIOD
4,"C",7/19/2017
4,"C",7/22/2017
4,"R",7/22/2017
4,"R",7/26/2017

data:

with temp as (
        select 4 as ID, 'R' as Flag, to_date('07/26/2017', 'mm/dd/yyyy') as Period from dual union
        select 4 as ID, 'R' as Flag, to_date('07/22/2017', 'mm/dd/yyyy') as Period from dual union
        select 4 as ID, 'C' as Flag, to_date('07/22/2017', 'mm/dd/yyyy') as Period from dual union 
        select 4 as ID, 'C' as Flag, to_date('07/19/2017', 'mm/dd/yyyy') as Period from dual)

I would like to have a result which indicates the ID, and whether the max(Period) where the flag is "C" for this ID is greater than the respective max(Period) where the flag is "R".

My desired result would be (Since the max(Period) for C here is 22/7/2017 and the max(Period) for R is 26/07/2017:

ID  IS_GREATER
4   NO

I have thought about making a group by query with ID and Flag and max(Period) and then doing a case statement. As I am new to this, I was wondering is there a better way?

Upvotes: 0

Views: 32

Answers (1)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

with temp
     as (select 4 as ID, 'R' as Flag, to_date('07/26/2017', 'mm/dd/yyyy') as Period from dual
         union
         select 4 as ID, 'R' as Flag, to_date('07/22/2017', 'mm/dd/yyyy') as Period from dual
         union
         select 4 as ID, 'C' as Flag, to_date('07/22/2017', 'mm/dd/yyyy') as Period from dual
         union
         select 4 as ID, 'C' as Flag, to_date('07/19/2017', 'mm/dd/yyyy') as Period from dual
         union
         select 5 as ID, 'R' as Flag, to_date('07/26/2016', 'mm/dd/yyyy') as Period from dual
         union
         select 5 as ID, 'R' as Flag, to_date('07/22/2016', 'mm/dd/yyyy') as Period from dual
         union
         select 5 as ID, 'C' as Flag, to_date('07/22/2017', 'mm/dd/yyyy') as Period from dual
         union
         select 5 as ID, 'C' as Flag, to_date('07/19/2017', 'mm/dd/yyyy') as Period from dual)
  select ID
       , case
           when max(case when FLAG = 'C' then Period else null end) > max(case when FLAG = 'R' then Period else null end) then
             'Y'
           else
             'N'
         end
           is_greater
    from temp
group by ID;

Upvotes: 1

Related Questions