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 44 45 46 47 48 49 50 51 52 53 54 55 56
| WITH tnbJO AS ( SELECT count(*) as nbJO
FROM `t_calendrier`
WHERE DATE(DTJOUR_DT) BETWEEN CURDATE() - INTERVAL 1 MONTH AND CURDATE()
AND TypeJour='O'
),
tetp AS ( SELECT t.ENTITEADR,
t.dom,
sum(t.uoadr) as sum_tache,
e.etp as nb_etp,
(sum(t.uoadr))/(e.etp) as tx_charge2
FROM t_data_adr_conf as t
INNER JOIN t_ref_etp as e
ON e.libentite = t.ENTITEADR
WHERE DATE(DTFINREL_DT) BETWEEN CURDATE() - INTERVAL 1 MONTH AND CURDATE()
AND t.dom in ( SELECT DISTINCT dom
FROM `t_data_adr_conf`
WHERE dom in ('THD-PRODUIT','2M','REA','MAJ AQD')
)
GROUP BY t.ENTITEADR, t.dom
ORDER BY t.ENTITEADR, t.dom
),
refetp AS ( SELECT val
FROM t_config
WHERE descr = 'ref_etp_min'
),
reftaux AS ( SELECT val
FROM t_config
WHERE descr = 'taux_etp'
),
autre AS ( SELECT tetp.dom,
tetp.ENTITEADR,
tetp.nb_etp,
tetp.sum_tache / ((tetp.nb_etp * (tnbJO.nbJO * refetp.val)) * reftaux.val) as tx_charge
FROM tnbJO,
tetp,
refetp,
reftaux
GROUP BY tetp.ENTITEADR,
tetp.dom
)
ajout AS ( SELECT tentite.entiteadr,
tdoma.dom,
autre.tx_charge
from ( SELECT distinct entiteadr
FROM `t_data_adr_conf`
) AS tentite,
( SELECT distinct dom
FROM `t_data_adr_conf`
) AS tdoma,
autre
; |
Partager