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
| WITH Facture_Realise AS
(
select
produit,
dl_piecebc,
ct_intitule,
SUM (CASE WHEN do_Piece LIKE 'FA%' THEN dl_qte ELSE 0 END) AS Facture,
SUM (CASE WHEN do_Piece LIKE 'BL%' THEN dl_qte ELSE 0 END) AS Realise,
MAX(CO_Prenom) AS Nom,
MAX(Consultant) as consultant1,
MAX(DL_DateBC) AS Date_BC
from cde_encours
where dl_piecebc <> ''
group by produit,dl_piecebc,ct_intitule
), A_Realiser AS
(
SELECT MAX(ct_intitule) as Client
,MAX(CO_Prenom) AS Nom2
,MAX(Consultant) as consultant2
,MAX(DL_DateBC) AS Date_BC2
,do_piece
,SUM (dl_qte) AS a_realiser
,max(produit) as produit2
FROM cde_encours
WHERE DL_PieceBC = ''
GROUP BY do_piece,ct_intitule,dl_piecebc
)
SELECT Client,Nom2 as Commercial,CASE WHEN consultant2 = '' THEN consultant1 ELSE consultant2 END as Consultant,Date_BC2 as [Date commande],do_piece as [N° commande],produit as Produit,CASE WHEN Facture IS NULL THEN 0 ELSE Facture END as Facturé,CASE WHEN Realise IS NULL THEN 0 ELSE Realise END as Realisé,a_realiser as [A réaliser],(CASE WHEN Facture IS NULL THEN 0 ELSE Facture END + CASE WHEN Realise IS NULL THEN 0 ELSE Realise END + a_realiser) as [Total commander]
FROM Facture_Realise fr
RIght JOIN A_Realiser a ON (fr.DL_PieceBC = a.do_piece)
where client = 'X'
order by client |
Partager