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
| SELECT
achc.NoLigneAchat AS Ligne,
achc.IndicateurObjet AS Type,
achc.CodeFourniPrestto AS 'Fourn/Prest',
achc.designation1 +',' + achc.designation2 +' ' + achc.designation3 +' ' + achc.designation4 +' ' + achc.designation5 AS 'Désignation',
achc.Varnumutil2 AS 'Qté cdée cond.',
achc.Varalphautil AS 'U Cond.',
CAST(achc.QuantiteCommandee AS varchar(20)) AS 'Qté ach.',
achc.UniteAchat AS 'U Achat',
CAST(achc.PrixUnitEnDevise AS varchar(20)) AS 'Prix unit',
CAST(achc.MtHtRemiseEscomp AS varchar(20)) AS 'Mtt HT',
achc.DateSouhaiteeplustard AS 'Date livraison',
achc.ResteRecevoir2 AS 'RàR',
bdrc.nobonrecepto AS 'Numéro de BR',
bdrc.quantiterecue AS 'Quantité recue'
t.Reste_a_recevoir
FROM ACHC
LEFT OUTER JOIN bdrc ON achc.numeroachat = bdrc.noconfirmation
INNER JOIN (
SELECT a.NoLigneAchat,
a.Varnumutil2 - SUM(b.quantiterecue) AS Reste_a_recevoir
FROM ACHC AS a
LEFT OUTER JOIN bdrc AS b ON achc.numeroachat = bdrc.noconfirmation
GROUP BY a.NoLigneAchat, a.Varnumutil2
) AS t ON ACHC.NoLigneAchat = t.NoLigneAchat
WHERE numeroachat = @i33101$
ORDER BY NoLigneAchat |
Partager