| lou_nyc 2005-09-30, 8:23 pm |
| select b. damage_inspection_da
te,
b. damage_inspection_by
,b.status
,NVL(a.cnt,0) CNT
from
(select aa. damage_inspection_da
te,
aa. damage_inspection_by
,
bb.status
from (select distinct trunc(gc. damage_inspection_da
te)
damage_inspection_da
te, gc. damage_inspection_by
from gate_damages gd, gate_containers gc
where gd.gate_id = gc.gate_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 damage_inspection_da
te,
damage_inspection_by
,
Status,
cnt
FROM (select trunc(c. damage_inspection_da
te)
damage_inspection_da
te,
c. damage_inspection_by
,
'MAJOR' STATUS,
count(distinct c.gate_id) cnt
from gate_containers c,
gate_damages d
where c.gate_id = d.gate_id and
d.damage_type_code = 'A'
group by
trunc(c. damage_inspection_da
te),c. damage_inspection_by
UNION ALL
select trunc(g. damage_inspection_da
te)
damage_inspection_da
te,
g. damage_inspection_by
,
'MINOR' STATUS,
count(distinct g.gate_id) cnt
from gate_containers g,
gate_damages z
where g.gate_id = z.gate_id and
z.damage_type_code = 'F'
group by
trunc(g. damage_inspection_da
te),g. damage_inspection_by
UNION ALL
select trunc(ab. damage_inspection_da
te)
damage_inspection_da
te,
ab. damage_inspection_by
,
'TOTAL' STATUS,
count(distinct ab.gate_id) cnt
from gate_containers ab,
gate_damages ac
where ab.gate_id = ac.gate_id
group by
trunc(ab. damage_inspection_da
te),ab. damage_inspection_by
)
group by damage_inspection_da
te, damage_inspection_by
,
status, cnt
)
) a
where b. damage_inspection_by
= a. damage_inspection_by
(+)
and b. damage_inspection_da
te = a. damage_inspection_da
te(+)
and b.status = a.status(+);
|