-- item=flanges, side=both, stepcond=itp20stat, firm=all
select maglist.magnet_nu,
maglist.step,
item.dx*1000 dx,
item.dz*1000 dz,
to_char(env.meas_date, 'dd-MON-yyyy') measdate
from
  (select magnet_nu, max(step) step from CRYOGEO_MEAS_ENV where magnet_nu in (select magnet_nu from CRYOGEO_MEAS_ENV where BLOCKAGE_STATUS='itp20stat') and step like 'WP08%' group by magnet_nu) maglist,
  (select magnet_nu, step, avg(dx) DX, avg(DZ) DZ from CRYOGEO_FLANGES	group by magnet_nu, step) item,
  (select magnet_nu, step, meas_date from CRYOGEO_MEAS_ENV) env
where maglist.magnet_nu=item.magnet_nu and maglist.magnet_nu=env.magnet_nu
and maglist.step=item.step and maglist.step=env.step

-- item=flanges, side=both, stepcond=itp20stat, firm=3
select maglist.magnet_nu,
maglist.step,
item.dx*1000 dx,
item.dz*1000 dz,
to_char(env.meas_date, 'dd-MON-yyyy') measdate
from
  (select magnet_nu, max(step) step from CRYOGEO_MEAS_ENV where magnet_nu in (select magnet_nu from CRYOGEO_MEAS_ENV where BLOCKAGE_STATUS='itp20stat') and step like 'WP08%' group by magnet_nu) maglist,
  (select magnet_nu, step, avg(dx) DX, avg(DZ) DZ from CRYOGEO_FLANGES group by magnet_nu, step) item,
  (select magnet_nu, step, meas_date from CRYOGEO_MEAS_ENV) env
where maglist.magnet_nu=item.magnet_nu and maglist.magnet_nu=env.magnet_nu
and maglist.step=item.step and maglist.step=env.step
and maglist.magnet_nu between 1000 and 1999

-- item=flanges, side=lyre, stepcond=lhcslot, firm=all
select maglist.magnet_nu,
maglist.step,
item.dx*1000 dx,
item.dz*1000 dz,
to_char(env.meas_date, 'dd-MON-yyyy') measdate
from
  (select magnet_nu, max(step) step from CRYOGEO_MEAS_ENV where magnet_nu in (select substr(part_id,-4) magnet_nu from ASBVIEWS.LB00@MAS2EDMS.WORLD where P4160 is not null) and step like 'WP08%' group by magnet_nu) maglist,
  (select magnet_nu, step, avg(dx) DX, avg(DZ) DZ from CRYOGEO_FLANGES	group by magnet_nu, step) item,
  (select magnet_nu, step, meas_date from CRYOGEO_MEAS_ENV) env
where maglist.magnet_nu=item.magnet_nu and maglist.magnet_nu=env.magnet_nu
and maglist.step=item.step and maglist.step=env.step

-- item=flanges, side=both, stepcond=WP08 firm=all
select maglist.magnet_nu,
maglist.step,
item.dx*1000 dx,
item.dz*1000 dz,
to_char(env.meas_date, 'dd-MON-yyyy') measdate
from
  (select magnet_nu, max(step) step from CRYOGEO_MEAS_ENV where step like 'WP08%' group by magnet_nu) maglist,
  (select magnet_nu, step, avg(dx) DX, avg(DZ) DZ from CRYOGEO_FLANGES	group by magnet_nu, step) item,
  (select magnet_nu, step, meas_date from CRYOGEO_MEAS_ENV) env
where maglist.magnet_nu=item.magnet_nu and maglist.magnet_nu=env.magnet_nu
and maglist.step=item.step and maglist.step=env.step


-- item=flanges, side=lyre, stepcond=itp20stat, firm=all
select maglist.magnet_nu,
maglist.step,
item.dx*1000 dx,
item.dz*1000 dz,
to_char(env.meas_date, 'dd-MON-yyyy') measdate
from
  (select magnet_nu, max(step) step from CRYOGEO_MEAS_ENV where magnet_nu in (select magnet_nu from CRYOGEO_MEAS_ENV where BLOCKAGE_STATUS='itp20stat') and step like 'WP08%' group by magnet_nu) maglist,
  (select magnet_nu, step, avg(dx) DX, avg(DZ) DZ from CRYOGEO_FLANGES where side like 'lyre' group by magnet_nu, step) item,
  (select magnet_nu, step, meas_date from CRYOGEO_MEAS_ENV) env
where maglist.magnet_nu=item.magnet_nu and maglist.magnet_nu=env.magnet_nu
and maglist.step=item.step and maglist.step=env.step

-- item=mcs, side=, stepcond=itp20stat, firm=all
select maglist.magnet_nu,
maglist.step,
item.dx*1000 dx,
item.dz*1000 dz,
to_char(env.meas_date, 'dd-MON-yyyy') measdate
from
  (select magnet_nu, max(step) step from CRYOGEO_MEAS_ENV where magnet_nu in (select magnet_nu from CRYOGEO_MEAS_ENV where BLOCKAGE_STATUS='itp20stat') and step like 'WP08%' group by magnet_nu) maglist,
  (select magnet_nu, step, avg(mcs_dx) DX, avg(mcs_DZ) DZ from CRYOGEO_DERIVED_DATA	group by magnet_nu, step) item,
  (select magnet_nu, step, meas_date from CRYOGEO_MEAS_ENV) env
where maglist.magnet_nu=item.magnet_nu and maglist.magnet_nu=env.magnet_nu
and maglist.step=item.step and maglist.step=env.step