select p.d_insp_date,
p.d_dam_date_by
,b.stat
,NVL(a.cnt,0) CNT
from
(select aa.dam_insp_date,
a.dam_insp_by,
bb.stat
from (select distinct trunc(gc.dam_insp_date) gc.dam_insp_date,
gc.dam_insp_by
from damages gd, gate_con gc
where gd.g_id = gc.g_id
) aa,
(select *
from (select 'MAJOR' STATUS from dual
union all
select 'MINOR' STATUS from dual
union all
select 'TOTAL' STATUS from dual
)
) bb
)b,
((SELECT dam_insp_date,
dam_insp_by,
Stat,
cnt
FROM (select trunc(c.dam_insp_date) dam_insp_date,
c.dam_insp_by,
'MAJOR' STATUS,
count(distinct c.g_id) cnt
from gate_cont c,
damages d
where c.gate_id = d.gate_id and
d.damage_type_code = 'A'
group by
trunc(c.damage_inspection_date),c.damage_inspection_by
UNION ALL
select trunc(g.damage_inspection_date)
damage_inspection_date,
g.damage_inspection_by,
'MINOR' STATUS,
count(distinct g.gate_id) cnt
from gate_containers g,
damages z
where g.gate_id = z.gate_id and
z.damage_type_code = 'F'
group by
trunc(g.damage_inspection_date),g.damage_inspection_by
UNION ALL
select trunc(ap.d_insp_date) damage_inspection_date,
ap.d_dam_date_by,
'TOTAL' STATUS,
count(distinct ab.gate_id) cnt
from gate_containers ab,
damages ac
where ab.gate_id = ac.gate_id
group by trunc(ap.d_insp_date),ap.d_dam_date_by
)
group by damage_inspection_date, damage_inspection_by,
status, cnt
)
) a
where p.d_dam_date_by = a.damage_inspection_by(+)
and p.d_insp_date = a.damage_inspection_date(+)
and b.status = a.status(+);
Received on Fri Sep 30 2005 - 14:41:54 CDT
