Fusionner 2 requêtes en 1

Version imprimable

Bonsoir,

Je souhaite fusionner 2 requêtes, afin d'avoir le résultat en 1 fois. N'étant pas très doué en sql, est ce qu'une âme charitable pourrait me donner un petit coup de pouce.

Je vous en remercie.

Cordialement
Laurent

Requête 1:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 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) order by 1, 2
Résultat 1 obtenu:
CD_COST_CENTER EQPTS TOT_PR
-------------------- ----- ------
valeur A valeur B Valeur C

Requête 2:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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) order by 1, 2
Résultat 2 obtenu:
CD_COST_CENTER EQPTS TOT_CU
-------------------- ----- ------
valeur A valeur B Valeur D

Résultat souhaité:
CD_COST_CENTER EQPTS TOT_PR TOT_CU
-------------------- ----- ------ ------
valeur A valeur B Valeur C Valeur D

De manière très bourrin, ca donne ca (mais c'est optimisable)

Code:

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

Oui il y a mieux que le FULL JOIN :

Code:

1
2
3
4
5
6
7
8
9
10
11
with cte_union (A, B, C, D) as
(
select A, B, C, null
  from table
 union all
select A, B, null, D
  from table
)
  select A, B, max(C) as C, max(D) as D
    from cte_union 
group by A, B;

Mais dans ce cas de figure présent, on peut réécrire la requête complètement :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
  select B.cd_cost_center
       , substr(A.cd_box, 1, 5) as eqpts
       , substr(sum(case when B.cd_action_type in ('PREV', 'PREV_N_PRO', 'INTERV_PRO') then C.pr_hour_cost * C.tm_occupation else 0 end) / 100000, 1, 6) as tot_PREV
       , substr(sum(case when B.cd_action_type = 'CUR'                                 then C.pr_hour_cost * C.tm_occupation else 0 end) / 100000, 1, 6) as tot_Cur
    from WO         A
    join WS         B  on B.cd_wo = A.cd_wo
    join OCCUPATION C  on C.cd_wo = A.cd_wo
                      and C.cd_ws = B.cd_ws
   where 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')
     and B.cd_action_type in ('CUR', 'PREV', 'PREV_N_PRO', 'INTERV_PRO')
group by B.cd_cost_center
       , substr(A.cd_box, 1, 5)
order by B.cd_cost_center       asc
       , substr(A.cd_box, 1, 5) asc;

01/04/2015, 17h43
laurent7474

Bonjour,

Excellent, exactement ce que je souhaitais.
Merci pour vos réponses.

Cordialement

Laurent