zOs/SQL/TECSVSB2

--- statistics and list of partitions
---       versus policy lastDataChange < fullCopy < unl --------------
set current application compatibility 'V11R1';
with c as
(
  select
        basTy  || case
           when value(lastDataChange, '2015-04-15-00.00.00')
                 <  basTst then 'c'

           when value(lastDataChange, '2015-04-15-00.00.00')
                 <  basTst + 1 day then '1'
           else '>' end
           || case when basTst <  unltst then 'b'
                   when basTst <  unltst + 1 day then '1'
                   else '>' end cat
         , v.*
    from oa1p.VQZ005RECOVDELTALOAD v
    where stage = 'UL' and unlTst > '2016-01-01-00.00.00'
)
select date(unlTst) unl, count(*) cnt
    , sum(case when cat = 'Fcb' then 1 else 0 end) "Fcb"
    , sum(case when cat = 'F>b' then 1 else 0 end) "F>b"
    , sum(case when cat = 'A1b' then 1 else 0 end) "A1b"
    , sum(case when cat = 'Y>b' then 1 else 0 end) "Y>b"
    , sum(case when cat in ('Fcb', 'F>b','A1b','Y>b')
    then 0 else 1 end)other
    from c
    group by rollup(date(unlTst))
    order by 1 desc
    with ur
;
with c as
(
  select
        basTy  || case
           when value(lastDataChange, '2015-04-15-00.00.00')
                 <  basTst then 'c'

           when value(lastDataChange, '2015-04-15-00.00.00')
                 <  basTst + 1 day then '1'
           else '>' end
           || case when basTst <  unltst then 'b'
                   when basTst <  unltst + 1 day then '1'
                   else '>' end cat
         , v.*
    from oa1p.VQZ005RECOVDELTALOAD v
    where stage = 'UL' and unlTst > '2016-01-01-00.00.00'
)
select unlTst, c.*
    from c
    where cat <> 'Fcb'
    order by unlTst, db, ts, pa