zOs/SQL/REOTSTK

set current sqlid = 's100447';
drop   view s100447.vReoRunRng2 ;
create view s100447.vReoRunRng2 as
with r1 as
(  select p.tst, p.rng, sta
         , count(*) cnt
         , count(reoTst) cntReoTst
         , max(case when reoTst>tst then reotst else  tst end) reoEnd
    from s100447.tReoRunPart p
    group by tst, sta, rng
) , r2 as
(
    select r1.*,
              (select max(sta)
                   from s100447.tReoRunPart p
                   where r1.tst = p.tst and r1.rng-1 = p.rng
                                            ) sta1
        from r1
        where sta = 'r' and cnt = cntReoTst
) , r3 as
(
    select r2.*,
              (select max(b.reoEnd)
                   from r1 b
                   where r2.tst = b.tst
                   and r2.rng-(case when r2.sta1='0' then 2 else 1 end)
                       = b.rng
                   and b.cnt = b.cntReoTst
              ) befEn1
        from r2
) , r4 as
(
    select r3.*,
        case when rng = 1 or (rng = 2 and sta1 = '0') then tst
             else befEn1 end befEnd
        from r3
)
select
        (days(reoEnd) - days(befEnd)) * 86400
        + midnight_seconds(reoEnd) - midnight_seconds(befEnd) rngTime,
        r4.*
    from r4
    where befEnd is not null
;
select * from s100447.vReoRunRng2
      where tst = '2011-09-12-11.32.03.461038'
;
commit
;;
select * from r4
    where tst = '2011-09-12-11.32.03.461038'
;;
) , r2 as
( select r.*,
      ( select coalesce(max(coalesce(o.reoEnd, o.tst))
                 , r.tst)
            from r1 o
            where o.tst = r.tst
                and o.rng <> r.rng and o.reoEnd < r.reoEnd
       ) reoBeg
    from r1 r
)