with kessan as ( 
  select
    a.�ØŒ”ƒR�[ƒh
    , c.‰ïŽÐ–¼
    , a.ŒˆŽZŠúŠÔ
    , a.˜AŒ‹ŒÂ•Ê
    , b.Šú–– "‘OŠúŠú––"
    , trunc( 
      ( 
        coalesce(c.q”„�ã�‚, c.”„�ã�‚) - coalesce(b.q”„�ã�‚, b.”„�ã�‚)
      ) / abs( 
        case coalesce(b.q”„�ã�‚, b.”„�ã�‚) 
          when 0 then 1 
          else coalesce(b.q”„�ã�‚, b.”„�ã�‚) 
          end
      ) 
      , 2
    ) * 100 "‘OŠú”„�ã�‚‘�‰Á—¦%"
    , trunc( 
      ( 
        coalesce(c.qŒo�í—˜‰v, c.Œo�í—˜‰v) - coalesce(b.qŒo�í—˜‰v, b.Œo�í—˜‰v)
      ) / abs( 
        case coalesce(b.qŒo�í—˜‰v, b.Œo�í—˜‰v) 
          when 0 then 1 
          else coalesce(b.qŒo�í—˜‰v, b.Œo�í—˜‰v) 
          end
      ) 
      , 2
    ) * 100 "‘OŠúŒo�í—˜‰v‘�‰Á—¦%"
    , trunc( 
      ( 
        coalesce(c.q“–Šú�ƒ—˜‰v, c.“–Šú�ƒ—˜‰v) - coalesce(b.q“–Šú�ƒ—˜‰v, b.“–Šú�ƒ—˜‰v)
      ) / abs( 
        case coalesce(b.q“–Šú�ƒ—˜‰v, b.“–Šú�ƒ—˜‰v) 
          when 0 then 1 
          else coalesce(b.q“–Šú�ƒ—˜‰v, b.“–Šú�ƒ—˜‰v) 
          end
      ) 
      , 2
    ) * 100 "‘OŠú“–Šú�ƒ—˜‰v‘�‰Á—¦%"
    , trunc( 
      ( 
        coalesce(b.q”„�ã�‚, b.”„�ã�‚) - coalesce(a.q”„�ã�‚, a.”„�ã�‚)
      ) / abs( 
        case coalesce(a.q”„�ã�‚, a.”„�ã�‚) 
          when 0 then 1 
          else coalesce(a.q”„�ã�‚, a.”„�ã�‚) 
          end
      ) 
      , 2
    ) * 100 "‘O�XŠú”„�ã�‚‘�‰Á—¦%"
    , trunc( 
      ( 
        coalesce(b.qŒo�í—˜‰v, b.Œo�í—˜‰v) - coalesce(a.qŒo�í—˜‰v, a.Œo�í—˜‰v)
      ) / abs( 
        case coalesce(a.qŒo�í—˜‰v, a.Œo�í—˜‰v) 
          when 0 then 1 
          else coalesce(a.qŒo�í—˜‰v, a.Œo�í—˜‰v) 
          end
      ) 
      , 2
    ) * 100 "‘O�XŠúŒo�í—˜‰v‘�‰Á—¦%"
    , trunc( 
      ( 
        coalesce(b.q“–Šú�ƒ—˜‰v, b.“–Šú�ƒ—˜‰v) - coalesce(a.q“–Šú�ƒ—˜‰v, a.“–Šú�ƒ—˜‰v)
      ) / abs( 
        case coalesce(a.q“–Šú�ƒ—˜‰v, a.“–Šú�ƒ—˜‰v) 
          when 0 then 1 
          else coalesce(a.q“–Šú�ƒ—˜‰v, a.“–Šú�ƒ—˜‰v) 
          end
      ) 
      , 2
    ) * 100 "‘O�XŠú“–Šú�ƒ—˜‰v‘�‰Á—¦%"
    , trunc( 
      (b.ˆêŠ”“–‚è�ƒ—˜‰v - a.ˆêŠ”“–‚è�ƒ—˜‰v) / abs(case a.ˆêŠ”“–‚è�ƒ—˜‰v when 0 then 1 else a.ˆêŠ”“–‚è�ƒ—˜‰v end)
      , 2
    ) * 100 "‘O�XŠúEPS‘�‰Á—¦%"
    , trunc( 
      (c.ˆêŠ”“–‚è�ƒ—˜‰v - b.ˆêŠ”“–‚è�ƒ—˜‰v) / abs(case b.ˆêŠ”“–‚è�ƒ—˜‰v when 0 then 1 else b.ˆêŠ”“–‚è�ƒ—˜‰v end)
      , 2
    ) * 100 "‘OŠúEPS‘�‰Á—¦%"
    , coalesce(a.q”„�ã�‚, a.”„�ã�‚) as "-2Šú”„�ã�‚"
    , coalesce(b.q”„�ã�‚, b.”„�ã�‚) as "-1Šú”„�ã�‚"
    , coalesce(c.q”„�ã�‚, c.”„�ã�‚) as "-0Šú”„�ã�‚"
    , coalesce(a.qŒo�í—˜‰v, a.Œo�í—˜‰v) as "-2ŠúŒo�í—˜‰v"
    , coalesce(b.qŒo�í—˜‰v, b.Œo�í—˜‰v) as "-1ŠúŒo�í—˜‰v"
    , coalesce(c.qŒo�í—˜‰v, c.Œo�í—˜‰v) as "-0ŠúŒo�í—˜‰v"
    , coalesce(a.q“–Šú�ƒ—˜‰v, a.“–Šú�ƒ—˜‰v) as "-2Šú“–Šú�ƒ—˜‰v"
    , coalesce(b.q“–Šú�ƒ—˜‰v, b.“–Šú�ƒ—˜‰v) as "-1Šú“–Šú�ƒ—˜‰v"
    , coalesce(c.q“–Šú�ƒ—˜‰v, c.“–Šú�ƒ—˜‰v) as "-0Šú“–Šú�ƒ—˜‰v"
    , a.ˆêŠ”“–‚è�ƒ—˜‰v as "-2ŠúEPS"
    , b.ˆêŠ”“–‚è�ƒ—˜‰v as "-1ŠúEPS"
    , c.ˆêŠ”“–‚è�ƒ—˜‰v as "-0ŠúEPS" 
  from
    ŒˆŽZ‚e c 
    inner join ( 
      select distinct
        d1.�ØŒ”ƒR�[ƒh
        , d1.˜AŒ‹ŒÂ•Ê
        , d1.ŒˆŽZŠúŠÔ
        , first_value(d1.ŠúŽñ) over (partition by d1.�ØŒ”ƒR�[ƒh, d1.˜AŒ‹ŒÂ•Ê, d1.ŒˆŽZŠúŠÔ) "0Šú"
        , nth_value(d1.ŠúŽñ, 2) over (partition by d1.�ØŒ”ƒR�[ƒh, d1.˜AŒ‹ŒÂ•Ê, d1.ŒˆŽZŠúŠÔ) "-1Šú"
        , nth_value(d1.ŠúŽñ, 3) over (partition by d1.�ØŒ”ƒR�[ƒh, d1.˜AŒ‹ŒÂ•Ê, d1.ŒˆŽZŠúŠÔ) "-2Šú" 
      from
        ( 
          select distinct
            dd.�ØŒ”ƒR�[ƒh
            , dd.˜AŒ‹ŒÂ•Ê
            , dd.ŠúŽñ
            , dd.ŒˆŽZŠúŠÔ 
          from
            ŒˆŽZ‚e dd 
          order by
            dd.�ØŒ”ƒR�[ƒh
            , dd.˜AŒ‹ŒÂ•Ê
            , dd.ŒˆŽZŠúŠÔ
            , dd.ŠúŽñ desc
        ) d1
    ) d 
      on c.�ØŒ”ƒR�[ƒh = d.�ØŒ”ƒR�[ƒh 
      and c.˜AŒ‹ŒÂ•Ê = d.˜AŒ‹ŒÂ•Ê 
      and c.ŒˆŽZŠúŠÔ = d.ŒˆŽZŠúŠÔ 
      and c.ŠúŽñ = d."0Šú" 
    left join ŒˆŽZ‚e b 
      on c.�ØŒ”ƒR�[ƒh = b.�ØŒ”ƒR�[ƒh 
      and c.˜AŒ‹ŒÂ•Ê = b.˜AŒ‹ŒÂ•Ê 
      and c.ŒˆŽZŠúŠÔ = b.ŒˆŽZŠúŠÔ 
      and b.ŠúŽñ = d."-1Šú" 
    left join ŒˆŽZ‚e a 
      on c.�ØŒ”ƒR�[ƒh = a.�ØŒ”ƒR�[ƒh 
      and c.˜AŒ‹ŒÂ•Ê = a.˜AŒ‹ŒÂ•Ê 
      and c.ŒˆŽZŠúŠÔ = a.ŒˆŽZŠúŠÔ 
      and a.ŠúŽñ = d."-2Šú"
) 
select
  a.�ØŒ”ƒR�[ƒh
  , a.‰ïŽÐ–¼
  , a.˜AŒ‹ŒÂ•Ê
  , e.�Å�VŒˆŽZŒŽ
  , e.�Å�VŒˆŽZŠú
  , e.�Å�V�î•ñŒöŠJ“ú
  , a."‘OŠú”„�ã�‚‘�‰Á—¦%" "’ÊŠú”„�ã�‚‘�‰Á—¦"
  , b."‘OŠú”„�ã�‚‘�‰Á—¦%" "1q”„�ã�‚‘�‰Á—¦"
  , c."‘OŠú”„�ã�‚‘�‰Á—¦%" "2q”„�ã�‚‘�‰Á—¦"
  , d."‘OŠú”„�ã�‚‘�‰Á—¦%" "3q”„�ã�‚‘�‰Á—¦"
  , a."‘OŠúŒo�í—˜‰v‘�‰Á—¦%" "’ÊŠúŒo�í—˜‰v‘�‰Á—¦"
  , b."‘OŠúŒo�í—˜‰v‘�‰Á—¦%" "1qŒo�í—˜‰v‘�‰Á—¦"
  , c."‘OŠúŒo�í—˜‰v‘�‰Á—¦%" "2qŒo�í—˜‰v‘�‰Á—¦"
  , d."‘OŠúŒo�í—˜‰v‘�‰Á—¦%" "3qŒo�í—˜‰v‘�‰Á—¦"
  , a."‘OŠú“–Šú�ƒ—˜‰v‘�‰Á—¦%" "’ÊŠú�ƒ—˜‰v‘�‰Á—¦"
  , b."‘OŠú“–Šú�ƒ—˜‰v‘�‰Á—¦%" "1q�ƒ—˜‰v‘�‰Á—¦"
  , c."‘OŠú“–Šú�ƒ—˜‰v‘�‰Á—¦%" "2q�ƒ—˜‰v‘�‰Á—¦"
  , d."‘OŠú“–Šú�ƒ—˜‰v‘�‰Á—¦%" "3q�ƒ—˜‰v‘�‰Á—¦"
  , a."‘OŠúEPS‘�‰Á—¦%" "’ÊŠúeps‘�‰Á—¦"
  , a."‘O�XŠú”„�ã�‚‘�‰Á—¦%" "2Šú‘O”„�ã�‚‘�‰Á—¦"
  , a."‘O�XŠúŒo�í—˜‰v‘�‰Á—¦%" "2Šú‘OŒo�í—˜‰v‘�‰Á—¦"
  , a."‘O�XŠú“–Šú�ƒ—˜‰v‘�‰Á—¦%" "2Šú‘O�ƒ—˜‰v‘�‰Á—¦"
from
  kessan a 
  left join kessan b 
    on a.�ØŒ”ƒR�[ƒh = b.�ØŒ”ƒR�[ƒh 
    and a.˜AŒ‹ŒÂ•Ê = b.˜AŒ‹ŒÂ•Ê 
    and b.ŒˆŽZŠúŠÔ = '‘æ1Žl”¼Šú' 
  left join kessan c 
    on a.�ØŒ”ƒR�[ƒh = c.�ØŒ”ƒR�[ƒh 
    and a.˜AŒ‹ŒÂ•Ê = c.˜AŒ‹ŒÂ•Ê 
    and c.ŒˆŽZŠúŠÔ = '‘æ2Žl”¼Šú' 
  left join kessan d 
    on a.�ØŒ”ƒR�[ƒh = d.�ØŒ”ƒR�[ƒh 
    and a.˜AŒ‹ŒÂ•Ê = d.˜AŒ‹ŒÂ•Ê 
    and d.ŒˆŽZŠúŠÔ = '‘æ3Žl”¼Šú' 
  inner join ( 
    select distinct
      �ØŒ”ƒR�[ƒh
      , ˜AŒ‹ŒÂ•Ê
      , first_value(Šú––) over (partition by �ØŒ”ƒR�[ƒh, ˜AŒ‹ŒÂ•Ê order by Šú–– desc) "�Å�VŒˆŽZŒŽ"
      , first_value(ŒˆŽZŠúŠÔ) over (partition by �ØŒ”ƒR�[ƒh, ˜AŒ‹ŒÂ•Ê order by Šú–– desc) "�Å�VŒˆŽZŠú"
      , first_value(�î•ñŒöŠJ“ú) over (partition by �ØŒ”ƒR�[ƒh, ˜AŒ‹ŒÂ•Ê order by Šú–– desc) "�Å�V�î•ñŒöŠJ“ú" 
    from
      ŒˆŽZ‚e
  ) e 
    on a.�ØŒ”ƒR�[ƒh = e.�ØŒ”ƒR�[ƒh 
    and a.˜AŒ‹ŒÂ•Ê = e.˜AŒ‹ŒÂ•Ê 
where
  a.ŒˆŽZŠúŠÔ = '’ÊŠú' 

-- �ØŒ”ƒR�[ƒh‚ðŽw’è‚·‚é�ðŒ��iex.�V�‚’l–Á•¿ƒR�[ƒh‚ðŽw’è�j
  and a.�ØŒ”ƒR�[ƒh in ( 
  'XXXX',
  'YYYY',
  'ZZZZ'
  ) 

-- Š”‚ÌŒöŽ®‚̃tƒ@ƒ“ƒ_ƒ�ƒ“ƒ^ƒ‹�ðŒ�‚É‘¦‚µ‚½�ðŒ�
--  and a."‘OŠú”„�ã�‚‘�‰Á—¦%" >= 10 
--  and b."‘OŠú”„�ã�‚‘�‰Á—¦%" >= 10 
--  and c."‘OŠú”„�ã�‚‘�‰Á—¦%" >= 10 
--  and d."‘OŠú”„�ã�‚‘�‰Á—¦%" >= 10 
--  and a."‘OŠúŒo�í—˜‰v‘�‰Á—¦%" >= 20 
--  and b."‘OŠúŒo�í—˜‰v‘�‰Á—¦%" >= 20 
--  and c."‘OŠúŒo�í—˜‰v‘�‰Á—¦%" >= 20 
--  and d."‘OŠúŒo�í—˜‰v‘�‰Á—¦%" >= 20 
--  and a."‘OŠú“–Šú�ƒ—˜‰v‘�‰Á—¦%" >= 20 
--  and b."‘OŠú“–Šú�ƒ—˜‰v‘�‰Á—¦%" >= 20 
--  and c."‘OŠú“–Šú�ƒ—˜‰v‘�‰Á—¦%" >= 20 
--  and d."‘OŠú“–Šú�ƒ—˜‰v‘�‰Á—¦%" >= 20 
--  and a."‘O�XŠú”„�ã�‚‘�‰Á—¦%" >= 5 
--  and a."‘O�XŠúŒo�í—˜‰v‘�‰Á—¦%" >= 10 
--  and a."‘O�XŠú“–Šú�ƒ—˜‰v‘�‰Á—¦%" >= 10

order by
  a.�ØŒ”ƒR�[ƒh
  , a.˜AŒ‹ŒÂ•Ê
  , a.ŒˆŽZŠúŠÔ