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
| declare @Tickets table
(
DATE_ACHAT DATETIME,
ID_VOL VARCHAR(7)
);
declare @Vols table
(
DATE_VOL DATETIME,
ID_VOL VARCHAR(7),
ID_AEP VARCHAR(3)
);
insert into @Tickets (DATE_ACHAT, ID_VOL) values ('2010-05-01 11:00:00', 'AF447');
insert into @Tickets (DATE_ACHAT, ID_VOL) values ('2010-05-01 11:35:00', 'AF445');
insert into @Vols (DATE_VOL, ID_VOL, ID_AEP) values('2010-05-01 11:50:00', 'AF447', 'BKK');
insert into @Vols (DATE_VOL, ID_VOL, ID_AEP) values('2010-05-02 12:35:00', 'AF447', 'RST');
insert into @Vols (DATE_VOL, ID_VOL, ID_AEP) values('2010-05-03 13:32:00', 'AF447', 'KBL');
insert into @Vols (DATE_VOL, ID_VOL, ID_AEP) values('2010-05-01 12:50:00', 'AF445', 'MRC');
insert into @Vols (DATE_VOL, ID_VOL, ID_AEP) values('2010-05-02 13:35:00', 'AF445', 'NCE');
insert into @Vols (DATE_VOL, ID_VOL, ID_AEP) values('2010-05-03 14:32:00', 'AF445', 'OSL');
with SR (DATE_ACHAT, ID_VOL, ID_AEP, RN)
as
(
select ti.DATE_ACHAT, ti.ID_VOL, vl.ID_AEP,
row_number() over(partition by ti.ID_VOL order by vl.DATE_VOL asc)
from @Tickets as ti
inner join @Vols as vl
on vl.ID_VOL = ti.ID_VOL
where ti.DATE_ACHAT < vl.DATE_VOL
)
select DATE_ACHAT, ID_VOL, ID_AEP
from SR
where RN = 1;
DATE_ACHAT ID_VOL ID_AEP
----------------------- ------- ------
2010-05-01 11:35:00.000 AF445 MRC
2010-05-01 11:00:00.000 AF447 BKK |
Partager