zOs/SQL/REORUNJP

set current sqlid = 'S100447';
create view s100447.vReoRunJobPart as
with p as
(select tst, ty, count(*) cnt,
     count(reotst) cntReo,
     min(reoTst) reoTstVon,
     max(reoTst) reoTstBis
    from s100447.tReoRunPart
    where sta = 'r'
    group by tst, ty
), j as
(
select j.*,
       p.ty pTy, p.cnt pCnt, p.cntReo,
       p.reoTstVon, p.reoTstBis,
       (select min(tst)
            from s100447.tReoRunJob a
            where a.job = j.job and a.tst > j.tst
       ) nextJob
    from s100447.tReoRunJob j
      left join p
        on j.tst = p.tst and p.ty = lower(left(j.ty, 1))
)
select  case when eoj is null and nextJob is not null
                 then 'null not last'
             when reoTstVon < tst then 'reoTst < tst'
             when reoTstBis > nextJob then 'reoTst > nextJob'
             when reoTstBis > eoj then 'reoTst > eoj'
             when eoj > nextJob then 'overlap nextJob'
             when reoTstBis - tst > 1000000 then 'reoTst > 1 day'
      --     when eoj - tst > 1000000 then 'eoj > 1 day'
             else null
        end e,
        case when pCnt is null or pCnt < 1 then tst
             when reoTstBis is not null
                  and (nextJob is null or nextJob > reoTstBis)
                  and (tst < current timestamp - 2 days
                        or pCnt = cntReo) then reoTstBis
             when nextJob is not null then
                  nextJob
             else null
        end eo2, j.*
    from j
;
select eoj, j.*
    from s100447.vReoRunJobPart j
    where e is not null
    order by job, tst desc
    with ur
;
update s100447.tReoRunJob j
    set eoj = (select eo2
                   from s100447.vReoRunJobPart e
                   where e.job = j.job and e.tst = j.tst)
    where (job, tst) in
          (select job, tst from s100447.vReoRunJobPart e
                           where e.e is not null
          )
;
select *
    from s100447.vReoRunJobPart
    where e is not null
    order by job, tst desc
    with ur
;
commit
;;;;
with p as
(select tst, ty, count(*) cnt,
     count(reotst) cntReoTst,
     sum(case when part = paVon then 1 else 0 end) cntReoRng,
     min(reoTst) reoTstVon,
     max(reoTst) reoTstBis
    from s100447.tReoRunPart
    group by tst, ty
)
select j.job, j.tst, j.eoj, p.ty, p.reoTstBis, p.*, j.*
    from s100447.tReoRunJob j
      left join p
        on j.tst = p.tst and p.ty = lower(left(j.ty, 1))
    where j.tst > current timestamp - 2 day
    order by j.job, j.tst desc
    with ur