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
| with VTE (Numfac, DatVte, Numero) as
(select 'FAC1', '2018-02-01', 'ref123'
UNION ALL
select 'FAC1', '2018-02-01', 'ref456'
UNION ALL
select 'FAC2', '2018-02-12', 'ref123'
)
, ACH (NumAch, DatAch, Numero, MtEuro) AS
(select 'ACH1', '2018-01-01', 'ref123', 005.00
union all
select 'ACH2', '2018-02-11', 'ref123', 010.00
union all
select 'ACH2', '2018-02-11', 'ref789', 007.00
union all
select 'ACH3', '2018-01-26', 'ref456', 009.00
union all
select 'ACH4', '2018-02-15', 'ref456', 014.00
union all
select 'ACH5', '2018-02-28', 'ref123', 020.00
)
select VT.NumFac
, VT.DatVte
, VT.Numero
, AC.MtEuro
from VTE as VT
inner join ACH as AC
on AC.Numero = VT.Numero
and AC.DatAch =
(select max(DatAch)
from ACH as SUBQ
where SUBQ.Numero= AC.Numero
and SUBQ.DatAch<=VT.DatVte)
order by VT.Numfac |
Partager