zOs/SQL/LOGEXACM

with n as
(
  select 'new' on
      , (select count(*)
           from oa1p.tqz065Abend o
           where t.timestamp       = o.timestamp
             and t.rz              = o.rz
             and t.dbSys           = o.dbSys
             and t.ssid            = o.ssid
             and t.EVENT_TYPE      = o.EVENT_TYPE
             and t.CORRID_ID       = o.CORRID_ID
             and t.JOBNAME         = o.JOBNAME
             and t.CONN_ID         = o.CONN_ID
             and t.AUTHID          = o.AUTHID
             and t.ASID            = o.ASID
             and t.TCB             = o.TCB
        ) othCnt
      , t.* from A540769.tqz065Abend t
  where rz = 'RZ2'
    and timestamp between '2014-10-05-04.00.00'
                      and '2014-10-09-00.00.00'
union all
  select 'old' on
      , (select count(*)
           from a540769.tqz065Abend o
           where t.timestamp       = o.timestamp
             and t.rz              = o.rz
             and t.dbSys           = o.dbSys
             and t.ssid            = o.ssid
             and t.EVENT_TYPE      = o.EVENT_TYPE
             and t.CORRID_ID       = o.CORRID_ID
             and t.JOBNAME         = o.JOBNAME
             and t.CONN_ID         = o.CONN_ID
             and t.AUTHID          = o.AUTHID
             and t.ASID            = o.ASID
             and t.TCB             = o.TCB
        ) othCnt
      , t.* from oa1p.tqz065Abend t
  where rz = 'RZ2'
    and timestamp between '2014-10-05-04.00.00'
                      and '2014-10-09-00.00.00'
)
select min(on), max(on)
      , othCnt
      , timestamp
      , rz
      , dbSys
      , ssid
      , EVENT_TYPE
      , CORRID_ID
      , JOBNAME
      , CONN_ID
      , AUTHID
      , ASID
      , TCB
    from n
--  where othCnt <> 1
    group by   othCnt
             , timestamp
             , rz
             , dbSys
             , ssid
             , EVENT_TYPE
             , CORRID_ID
             , JOBNAME
             , CONN_ID
             , AUTHID
             , ASID
             , TCB
--  having min(on) = max(on)
    order by timestamp, rz, dbSys, event_type, min(on)