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 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94
|
select
A.Code, A.Reference, a.designation, a.couleur, a.fournisseur, a.quantite ,
COALESCE(b.Quant_RL_1,0) + COALESCE(c.Quant_INV_1,0) + COALESCE(e.Quant_TE_1,0) - COALESCE(d.Quant_VL_2020_1,0) - COALESCE(f.Quant_TS_1,0) as Quant1,
COALESCE(b.Quant_RL_2,0) + COALESCE(c.Quant_INV_2,0) + COALESCE(e.Quant_TE_2,0) - COALESCE(d.Quant_VL_2020_2,0) - COALESCE(f.Quant_TS_2,0) as Quant2,
COALESCE(b.Quant_RL_3,0) + COALESCE(c.Quant_INV_3,0) + COALESCE(e.Quant_TE_3,0) - COALESCE(d.Quant_VL_2020_3,0) - COALESCE(f.Quant_TS_3,0) as Quant3,
COALESCE(b.Quant_RL_4,0) + COALESCE(c.Quant_INV_4,0) + COALESCE(e.Quant_TE_4,0) - COALESCE(d.Quant_VL_2020_4,0) - COALESCE(f.Quant_TS_4,0) as Quant4,
COALESCE(b.Quant_RL_5,0) + COALESCE(c.Quant_INV_5,0) + COALESCE(e.Quant_TE_5,0) - COALESCE(d.Quant_VL_2020_5,0) - COALESCE(f.Quant_TS_5,0) as Quant5,
COALESCE(b.Quant_RL_6,0) + COALESCE(c.Quant_INV_6,0) + COALESCE(e.Quant_TE_6,0) - COALESCE(d.Quant_VL_2020_6,0) - COALESCE(f.Quant_TS_6,0) as Quant6
from
(
SELECT
V.IDproduit AS Code,
P.produit_ref_produit AS Reference,
P.produit_nom_produit AS Designation,
PC.Couleur_Nom AS Couleur,
F.Nom_fournisseur AS Fournisseur,
SUM(V.Panier_Ligne_Quantite) AS Quantite
FROM
VENTE_LIGNE_2020 V
left outer join PRODUIT P ON V.IDproduit = P.IDproduit
left outer join PRODUIT_COULEUR PC ON PC.IDcouleur = P.IDcouleur
left outer join FOURNISSEUR F ON F.IDfournisseur = P.IDfournisseur
GROUP BY
V.IDproduit,
P.produit_ref_produit,
P.produit_nom_produit,
PC.Couleur_Nom,
F.Nom_fournisseur
) a,
(
SELECT RL.Idproduit , sum (case when R.IdMagasin = 1 then RL.reception_ligne_quantite else null end ) AS Quant_RL_1,
sum (case when R.IdMagasin = 2 then RL.reception_ligne_quantite else null end ) AS Quant_RL_2,
sum (case when R.IdMagasin = 3 then RL.reception_ligne_quantite else null end ) AS Quant_RL_3,
sum (case when R.IdMagasin = 4 then RL.reception_ligne_quantite else null end ) AS Quant_RL_4,
sum (case when R.IdMagasin = 5 then RL.reception_ligne_quantite else null end ) AS Quant_RL_5,
sum (case when R.IdMagasin = 6 then RL.reception_ligne_quantite else null end ) AS Quant_RL_6
FROM RECEPTION_LIGNE RL
LEFT OUTER JOIN RECEPTION R ON R.IDreception = RL.IDreception
GROUP BY RL.Idproduit
) b,
(
SELECT IL.Idproduit , sum (case when I.IdMagasin = 1 then IL.Inventaire_Ligne_Quantite_Comptee else null end ) AS Quant_INV_1,
sum (case when I.IdMagasin = 2 then IL.Inventaire_Ligne_Quantite_Comptee else null end ) AS Quant_INV_2,
sum (case when I.IdMagasin = 3 then IL.Inventaire_Ligne_Quantite_Comptee else null end ) AS Quant_INV_3,
sum (case when I.IdMagasin = 4 then IL.Inventaire_Ligne_Quantite_Comptee else null end ) AS Quant_INV_4,
sum (case when I.IdMagasin = 5 then IL.Inventaire_Ligne_Quantite_Comptee else null end ) AS Quant_INV_5,
sum (case when I.IdMagasin = 6 then IL.Inventaire_Ligne_Quantite_Comptee else null end ) AS Quant_INV_6
FROM INVENTAIRE_LIGNE IL
LEFT OUTER JOIN INVENTAIRE I ON I.IDinventaire = IL.IDinventaire
GROUP BY IL.Idproduit
) c,
(
SELECT VL_2020.Idproduit , sum (case when V_2020.IdMagasin = 1 then VL_2020.Panier_Ligne_Quantite else null end ) AS Quant_VL_2020_1,
sum (case when V_2020.IdMagasin = 2 then VL_2020.Panier_Ligne_Quantite else null end ) AS Quant_VL_2020_2,
sum (case when V_2020.IdMagasin = 3 then VL_2020.Panier_Ligne_Quantite else null end ) AS Quant_VL_2020_3,
sum (case when V_2020.IdMagasin = 4 then VL_2020.Panier_Ligne_Quantite else null end ) AS Quant_VL_2020_4,
sum (case when V_2020.IdMagasin = 5 then VL_2020.Panier_Ligne_Quantite else null end ) AS Quant_VL_2020_5,
sum (case when V_2020.IdMagasin = 6 then VL_2020.Panier_Ligne_Quantite else null end ) AS Quant_VL_2020_6
FROM VENTE_LIGNE_2020 VL_2020
LEFT OUTER JOIN VENTE_2020 V_2020 ON V_2020.IDpanier = VL_2020.IDpanier
GROUP BY VL_2020.Idproduit
) d,
(
SELECT TL.Idproduit,
sum (case when T.Transfert_Preparation_IDmagasin = 1 then TL.Transfert_Ligne_Quantite else null end ) AS Quant_TE_1,
sum (case when T.Transfert_Preparation_IDmagasin = 2 then TL.Transfert_Ligne_Quantite else null end ) AS Quant_TE_2,
sum (case when T.Transfert_Preparation_IDmagasin = 3 then TL.Transfert_Ligne_Quantite else null end ) AS Quant_TE_3,
sum (case when T.Transfert_Preparation_IDmagasin = 4 then TL.Transfert_Ligne_Quantite else null end ) AS Quant_TE_4,
sum (case when T.Transfert_Preparation_IDmagasin = 5 then TL.Transfert_Ligne_Quantite else null end ) AS Quant_TE_5,
sum (case when T.Transfert_Preparation_IDmagasin = 6 then TL.Transfert_Ligne_Quantite else null end ) AS Quant_TE_6
FROM TRANSFERT_LIGNE TL
LEFT OUTER JOIN TRANSFERT T ON T.IDtransfert = TL.IDtransfert
GROUP BY TL.Idproduit
) e,
(
SELECT TL.Idproduit,
sum (case when T.Transfert_Reception_IDmagasin = 1 then TL.Transfert_Ligne_Quantite else null end ) AS Quant_TS_1,
sum (case when T.Transfert_Reception_IDmagasin = 2 then TL.Transfert_Ligne_Quantite else null end ) AS Quant_TS_2,
sum (case when T.Transfert_Reception_IDmagasin = 3 then TL.Transfert_Ligne_Quantite else null end ) AS Quant_TS_3,
sum (case when T.Transfert_Reception_IDmagasin = 4 then TL.Transfert_Ligne_Quantite else null end ) AS Quant_TS_4,
sum (case when T.Transfert_Reception_IDmagasin = 5 then TL.Transfert_Ligne_Quantite else null end ) AS Quant_TS_5,
sum (case when T.Transfert_Reception_IDmagasin = 5 then TL.Transfert_Ligne_Quantite else null end ) AS Quant_TS_6
FROM TRANSFERT_LIGNE TL
LEFT OUTER JOIN TRANSFERT T ON T.IDtransfert = TL.IDtransfert
GROUP BY TL.Idproduit
) f
where b.idProduit (+)= a.Code AND c.IDproduit (+)=a.code AND d.IDproduit (+)=a.code AND e.IDproduit (+)=a.code AND f.IDproduit (+)=a.code
GROUP BY
A.Code, A.Reference, a.designation, a.couleur, a.fournisseur, a.quantite, quant1, quant2, quant3, quant4, quant5, quant6 |
Partager