1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| select coalesce(A.cd_cost_center, B.cd_cost_center) as cd_cost_center
,coalesce(A.eqpts, B.eqpts) as eqpts
,tot_PREV
,tot_Cur
FROM
(select
B.cd_cost_center, substr(A.cd_box,1, 5) eqpts, substr(sum(C.pr_hour_cost * C.tm_occupation)/100000,1, 6) tot_PREV
from
WO A
INNER JOIN WS B
on A.cd_wo = B.cd_wo
INNER JOIN OCCUPATION C
on C.cd_wo = A.cd_wo
and C.cd_ws = B.cd_ws
where
(
B.cd_action_type = 'PREV_N_PRO'
or
B.cd_action_type = 'INTERV_PRO'
or
B.cd_action_type = 'PREV'
)
and B.dt_ws_begin >= to_date('01022015 00:00:01','DDMMYYYY HH24:MI:SS')
and B.dt_ws_begin <= to_date('28022015 23:59:59','DDMMYYYY HH24:MI:SS')
group by B.cd_cost_center, substr(A.cd_box,1, 5)
) A
FULL OUTER JOIN
(
select
B.cd_cost_center, substr(A.cd_box,1, 4) eqpts, substr(sum(C.pr_hour_cost * C.tm_occupation)/100000,1, 6) tot_Cur
from
WO A
INNER JOIN WS B
on A.cd_wo = B.cd_wo
INNER JOIN OCCUPATION C
on C.cd_wo = A.cd_wo
and C.cd_ws = B.cd_ws
where B.cd_action_type = 'CUR'
and B.dt_ws_begin >= to_date('01022015 00:00:01','DDMMYYYY HH24:MI:SS')
and B.dt_ws_begin <= to_date('28022015 23:59:59','DDMMYYYY HH24:MI:SS')
group by B.cd_cost_center, substr(A.cd_box,1, 4)
) B ON A.cd_cost_center = B.cd_cost_center and A.eqpts = B.eqpts
order by 1,2 |
Partager