1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| DECLARE @table TABLE (
num_client CHAR(4)
, Type_Document VARCHAR(20)
, DateCreate DATETIME
, Num_Document VARCHAR(10)
, Montant INT
, Num_Document_Ext VARCHAR(10)
)
INSERT INTO @table (num_client, Type_Document, DateCreate, Num_Document, Montant, Num_Document_Ext)
VALUES
('0001', 'paiement', '20130301', '01', 100, null),
('001', 'paiement', '20130401', '01', 200, null),
('001', 'facture', '20130401', 'Fact01', 350, '01'),
('001', 'paiement', '20130102', '01', 50, null),
('002', 'paiement', '20130102', '01', 50, null)
SELECT T1.num_client, T1.Num_Document, T1.Montant AS MontantFacture, SUM(T2.Montant) AS ReglementFactureAvril
FROM @table T1
INNER JOIN @table T2 ON T1.Num_Document_Ext = T2.Num_Document
WHERE T1.Type_Document = 'facture' -- Num_Document_Ext IS NOT NULL
AND T2.DateCreate BETWEEN '20130401' AND '20130430'
GROUP BY T1.num_client, T1.Num_Document, T1.Montant |
Partager