zOs/SQL/TOP100

-- top 100 Packages mit Vergleich der letzten 12 Monate im RZ2
--   pro Monat: Rangm, cpu, chf
set current path oa1p;
with m (mon, pos) as
(
    select trunc_Timestamp(current timestamp, 'month') - 1 month
           , 1 from sysibm.sysDummy1
    union all select mon - 1 month, pos + 1
           from m where pos < 12
)
, p1 as
(
select rzDb, programName, trunc_timestamp(begin, 'month') mon
    , len
    , CPUTCBPKG
    , ( select i.price from pbSu.tacct_price i
          where i.kind = 'chfSu'
            and i.begin <= p.begin
            and i.end   >  p.begin) * suTcbPkg chf
    from pbSu.tacct_program p
    where cal = 'onlPik/4'
      and begin >= trunc_Timestamp(current timestamp, 'month')-12 month
      and begin <  trunc_Timestamp(current timestamp, 'month')
)
, p2 as
(
select rzDb, programName, mon
    , sum(len) len
    , sum(CPUTCBPKG) CPUTCBPKG
    , sum(chf) chf
    from p1
    group by rzDb, programName, mon
)
, p3 as
(
  select rzDb, programName, mon
     , case when programName = '*' then -1
            else rank() over (partition by mon
                     , case when programName = '*' then 0 else 1 end
                     order by chf desc)
       end rang
     , CPUTCBPKG
     , chf
    from p2
)
, t1 as
(
  select mon, max(len) len
      from p2
      where programName = '*'
      group by mon
)
, t as
(
  select t1.*, m.pos
      from t1 join m on t1.mon = m.mon
)
, p (rzDb, programName, mon, pos, rang, cpuSec, chf) as
(
  select p3.rzDb, p3.programName, p3.mon, t.pos
     , smallint(rang)
     , char(dec(p3.cpuTcbPkg / t.len, 5, 3))
    , int(round(chf, 0))
    from p3 join t on p3.mon = t.mon
  union all select '', '', mon, pos, smallint(-9)
    , substr(to_char(mon, 'mon yy'), 1, 6)
    , int(0)
    from m
)
select p01.rzDb "rzDb", p01.programName "program"
       , p01.rang "rang1", p01.cpuSec "cpu  1", p01.chf "chf  1"
       , p02.rang "rang2", p02.cpuSec "cpu  2", p02.chf "chf  2"
       , p03.rang "rang3", p03.cpuSec "cpu  3", p03.chf "chf  3"
       , p04.rang "rang4", p04.cpuSec "cpu  4", p04.chf "chf  4"
       , p05.rang "rang5", p05.cpuSec "cpu  5", p05.chf "chf  5"
       , p06.rang "rang6", p06.cpuSec "cpu  6", p06.chf "chf  6"
       , p07.rang "rang7", p07.cpuSec "cpu  7", p07.chf "chf  7"
       , p08.rang "rang8", p08.cpuSec "cpu  8", p08.chf "chf  8"
       , p09.rang "rang9", p09.cpuSec "cpu  9", p09.chf "chf  9"
       , p10.rang "ran10", p10.cpuSec "cpu 10", p10.chf "chf 10"
       , p11.rang "ran11", p11.cpuSec "cpu 11", p11.chf "chf 11"
       , p12.rang "ran12", p12.cpuSec "cpu 12", p12.chf "chf 12"
    from p p01
    left join p p02 on p02.pos = 2 and p02.rzDb = p01.rzDb
                                 and p02.programName = p01.programName
    left join p p03 on p03.pos = 3 and p03.rzDb = p01.rzDb
                                 and p03.programName = p01.programName
    left join p p04 on p04.pos = 4 and p04.rzDb = p01.rzDb
                                 and p04.programName = p01.programName
    left join p p05 on p05.pos = 5 and p05.rzDb = p01.rzDb
                                 and p05.programName = p01.programName
    left join p p06 on p06.pos = 6 and p06.rzDb = p01.rzDb
                                 and p06.programName = p01.programName
    left join p p07 on p07.pos = 7 and p07.rzDb = p01.rzDb
                                 and p07.programName = p01.programName
    left join p p08 on p08.pos = 8 and p08.rzDb = p01.rzDb
                                 and p08.programName = p01.programName
    left join p p09 on p09.pos = 9 and p09.rzDb = p01.rzDb
                                 and p09.programName = p01.programName
    left join p p10 on p10.pos =10 and p10.rzDb = p01.rzDb
                                 and p10.programName = p01.programName
    left join p p11 on p11.pos =11 and p11.rzDb = p01.rzDb
                                 and p11.programName = p01.programName
    left join p p12 on p12.pos =12 and p12.rzDb = p01.rzDb
                                 and p12.programName = p01.programName
    where p01.pos = 1
      and p01.programName in ('', '*', 'YXCWDOC')
    order by p01.rang