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 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
|
SELECT
id_lap
,id_cln
,id_cln_sup200
,id_sfa
,id_prp
,NVL(SUM ( mt_ric ),0) mt_ric
,NVL(SUM ( mt_cab ),0) mt_cab
,NVL(SUM ( mt_mep ),0) mt_mep
,NVL(SUM ( mt_mvb ),0) mt_mvb
,NVL(SUM ( qt_kgl ),0) qt_kgl
,NVL(SUM ( nb_fac ),0) nb_fac
,NVL(SUM ( nb_lig_fac ),0) nb_lig_fac
,NVL(SUM ( nb_liv ),0) nb_liv
,NVL(SUM ( nb_liv_sup10lg ),0) nb_liv_sup10lg
,NVL(SUM ( nb_liv_inf100e ),0) nb_liv_inf100e
,NVL(SUM ( nb_avo ),0) nb_avo
,NVL(SUM ( nb_cln_new ),0) nb_cln_new
,NVL(SUM ( nb_cln_vivier ),0) nb_cln_vivier
FROM
(SELECT id_lap
,id_cln
,id_cln_sup200
,id_sfa
,id_prp
,0 mt_ric
,NVL(SUM( can_fac ),0) mt_cab
,NVL(SUM( css_fac ),0) - NVL(SUM( pmp_fac ),0) mt_mep
,NVL(SUM( can_fac ),0) - NVL(SUM( css_fac ),0) mt_mvb
,NVL(SUM( qtk_fac ),0) qt_kgl
,COUNT(DISTINCT id_fac) nb_fac
,COUNT(DISTINCT id_fac || id_lgn) nb_lig_fac
,COUNT(DISTINCT dat_fac || trn_fac || id_cln) nb_liv
,COUNT(DISTINCT
CASE WHEN nb_lg_livraison > 10
THEN dat_fac || trn_fac || id_cln END) nb_liv_sup10lg
,COUNT(DISTINCT
CASE WHEN ca_par_livraison < 100
THEN dat_fac || trn_fac || id_cln END) nb_liv_inf100e
,COUNT(DISTINCT
CASE WHEN typ_fac IN ('ZG2','ZGF','ZGR2')
THEN id_fac END) nb_avo
,COUNT(DISTINCT lf.id_cln_new) nb_cln_new
,COUNT(DISTINCT lf.id_cln_vivier) nb_cln_vivier
FROM ( SELECT cal.id_lap
,base.id_fac
,base.id_cln
,CASE WHEN SUM(base.can_fac) OVER (PARTITION BY base.id_cln, cal.id_lap, base.id_per) > 200
THEN base.id_cln END id_cln_sup200
,base.dat_fac
,base.trn_fac
,base.typ_fac
,base.id_sfa
,TO_CHAR(NVL(base.id_prp,0)) id_prp
,base.id_lgn
,base.can_fac
,base.css_fac
,base.pmp_fac
,base.qtk_fac
,COUNT(DISTINCT base.id_lgn)
OVER (PARTITION BY base.id_cln, base.dat_fac, base.trn_fac) nb_lg_livraison
,SUM(base.can_fac)
OVER (PARTITION BY base.id_cln, base.dat_fac, base.trn_fac) ca_par_livraison
,CASE WHEN base.can_fac > 0 AND base.dcr_cln BETWEEN cal.id_sem AND cal.id_sem + 6
THEN base.id_cln END id_cln_new
,-- Regle de calcul a définir
CASE WHEN base.can_fac > 0 AND base.dcr_cln BETWEEN cal.id_sem AND cal.id_sem + 6
THEN base.id_cln END id_cln_vivier
FROM CUBE_NOMADE_MENSUEL$648175_1 base
,CALENDRIER_445$648175 cal
WHERE cal.id_sem = base.id_sem
) lf
GROUP BY
id_lap
,id_cln
,id_cln_sup200
,id_sfa
,id_prp |
Partager