zOs/SQL/QZ06SUM

with u (tb, cnt, rz, dbSys, ssMin, ssMax
       , tstMin, tstMax, loadMin, loadMax) as
(
select '60timeOut', count(*), rz, dbSys, min(ssid), max(ssid)
        , min(timestamp), max(timestamp)
        , min(loadDate), max(loadDate)
    from oa1p.tqz060Timeout
    where timestamp > '2015-05-01-00.00.00'
        and loadDate > '08.10.2014'
    group by left(ssid, 3), rz,dbsys
union all select '63UOW',  count(*), rz, dbSys, min(ssid), max(ssid)
        , min(timestamp), max(timestamp)
        , min(loadDate), max(loadDate)
    from oa1p.tqz063UOW
    where timestamp > '2015-05-01-00.00.00'
        and loadDate > '08.10.2014'
    group by left(ssid, 3), rz,dbsys
union all select '65LockEs', count(*), rz, dbSys, min(ssid), max(ssid)
        , min(timestamp), max(timestamp)
        , min(loadDate), max(loadDate)
    from oa1p.tqz064LockEscal
    where timestamp > '2015-05-01-00.00.00'
        and loadDate > '08.10.2014'
    group by left(ssid, 3), rz,dbsys
union all select '65Abend', count(*), rz, dbSys, min(ssid), max(ssid)
        , min(timestamp), max(timestamp)
        , min(loadDate), max(loadDate)
    from oa1p.tqz065Abend
    where timestamp > '2015-05-01-00.00.00'
        and loadDate > '08.10.2014'
    group by left(ssid, 3), rz,dbsys
)
select *
    from u
    order by 3, 4, 1
    with ur
;x;
select count(*), count(distinct loadDate), loadDate
    from oa1p.tqz060Timeout
    where rz = 'RR2' and loadDate > current date - 1 month
    group by loadDate
    order by 3 desc
;X;
select count(*), rz, dbSys, min(ssid), max(ssid)
        , min(timestamp), max(timestamp)
        , min(loadDate), max(loadDate)
    from oa1p.tqz060Timeout
    where timestamp > '2014-08-01-00.00.00'
        and loadDate > '08.10.2014'
    group by left(ssid, 3), rz,dbsys
       --, trunc_timestamp(timestamp, 'mon')
    order by 2, 3
    with ur
;
select count(*), rz, dbSys, min(ssid), max(ssid)
        , min(timestamp), max(timestamp)
        , min(loadDate), max(loadDate)
    from oa1p.tqz063UOW
    where timestamp > '2014-08-01-00.00.00'
        and loadDate > '08.10.2014'
    group by left(ssid, 3), rz,dbsys
       --, trunc_timestamp(timestamp, 'mon')
    order by 2, 3
    with ur
;
select count(*), rz, dbSys, min(ssid), max(ssid)
        , min(timestamp), max(timestamp)
        , min(loadDate), max(loadDate)
    from oa1p.tqz064LockEscal
    where timestamp > '2014-08-01-00.00.00'
        and loadDate > '08.10.2014'
    group by left(ssid, 3), rz,dbsys
       --, trunc_timestamp(timestamp, 'mon')
    order by 2, 3
    with ur
;
select count(*), rz, dbSys, min(ssid), max(ssid)
        , min(timestamp), max(timestamp)
        , min(loadDate), max(loadDate)
    from oa1p.tqz065Abend
    where timestamp > '2014-08-01-00.00.00'
        and loadDate > '08.10.2014'
    group by left(ssid, 3), rz,dbsys
       --, trunc_timestamp(timestamp, 'mon')
    order by 2, 3
    with ur
;x;