P5/20X/discoverPedestals.sql

0001 select
0002          r.runnumber as rNb
0003        , m.modedescription as modeT
0004        , p.partitionname as pName
0005        -- , a.analysistype
0006        -- , r.starttime as run_start
0007        -- , s.statehistorydate as state_date
0008        -- , s.fecversionmajorid||'.'||s.fecversionminorid as run_fec
0009        -- , s.fedversionmajorid||'.'||s.fedversionminorid as run_fed
0010        -- , s.connectionversionmajorid||'.'||s.connectionversionminorid as run_conn
0011        -- , s2.statehistorydate as newstate_date
0012        --, s2.fecversionmajorid||'.'||s2.fecversionminorid as new_fec
0013        , s2.fedversionmajorid||'.'||s2.fedversionminorid as new_fed
0014        --, s2.connectionversionmajorid||'.'||s2.connectionversionminorid as new_conn
0015        --, s2.dcuinfoversionmajorid||'.'||s2.dcuinfoversionminorid
0016       --, r.runmode
0017   from
0018     run r
0019   , partition p
0020   , modetype m
0021   , statehistory s
0022   , statehistory s2
0023   where
0024       m.runmode=r.runmode
0025   and r.runmode =2
0026  -- and s2.fedversionmajorid!=s.fedversionmajorid
0027   and s2.connectionversionmajorid!=2
0028   and r.partitionid=p.partitionid
0029   and r.partitionid=s.partitionid
0030   and r.statehistoryid=s.statehistoryid
0031   and s.partitionid = s2.partitionid
0032   and s2.statehistoryid = (
0033       select min(s3.statehistoryid)
0034       from statehistory s3
0035       where
0036           s3.statehistoryid > ( decode( s.statehistoryid, null, 0, s.statehistoryid) )
0037       and s3.partitionid = s.partitionid
0038   )
0039 and (
0040         r.runmode=2  or
0041         exists (
0042          select x.analysisid
0043          from analysis x
0044          where x.runnumber = r.runnumber
0045         and x.partitionid = r.partitionid
0046         )
0047 )
0048 and  r.runnumber>49800
0049 ;