SQL - How to link tables by Date

As an overview I have a sale that was generated and I need to link to it the PreSaleSnapshot record that existed just before the PreSaleSnapshot table was updated by the Sale.
Unfortunatly the only way to do this is on the Sale.EventDate and the PreSaleSnapshot.SnapshotDate
 
So I have 2 tables. 1st #Sale holds all the Sale records
and the 2nd #PreSaleSnapshot which holds all the Snapshot records that existed just before the Sale was made.

I am trying now to update #Sale with the correct PreSale.ClientAssetSnapshotID by doing this (see code below)

And it almost works! but not quite, the Sale table should read:

SALEID                  PRESALECASID

600001659             569755

600001750             570961

990021176             3324609

990051702             3972867

but it doesn't it reads instead:

SALEID                  PRESALECASID

600001659             570961

600001750             3324609

990021176             3972867

990051702             NULL

Can you help me fix it?

set nocount on  
 
 
Create TABLE #Sale(SaleID			int  NOT NULL ,
CALID			int Null, 
PreSaleCASID	int Null,
EventDate       DateTime Null,
UnitsHeld       numeric (15, 4))
 
insert into #Sale(SaleID, CALID, PreSaleCASID, EventDate, UnitsHeld) 
Values(600001659, 10000008, NULL, '2000-16-06 13:53:16.000', 0)
insert into #Sale(SaleID, CALID, PreSaleCASID, EventDate, UnitsHeld) 
Values(600001750, 10000008, NULL, '2000-23-06 09:29:06.000', 0)
insert into #Sale(SaleID, CALID, PreSaleCASID, EventDate, UnitsHeld) 
Values(990021176, 10000008, NULL, '2002-26-09 16:12:48.060', 0)
insert into #Sale(SaleID, CALID, PreSaleCASID, EventDate, UnitsHeld) 
Values(990051702, 10000008, NULL, '2003-17-03 12:13:26.717', 0)
 
select * from #Sale
 
Create TABLE #PreSaleSnapshot(CASID        int NULL ,
CALID		int Null, 
UnitsHeld    numeric(15,4) null,
SnapshotDate DateTime Null)
 
insert into #PreSaleSnapshot(CASID, CALID, UnitsHeld, SnapshotDate) 
Values(569755, 10000008, 238.4240, '2000-16-06 10:57:38.000')
insert into #PreSaleSnapshot(CASID, CALID, UnitsHeld, SnapshotDate) 
Values(570961, 10000008, 185.4240, '2000-16-06 13:53:16.000')
insert into #PreSaleSnapshot(CASID, CALID, UnitsHeld, SnapshotDate) 
Values(3324609, 10000008, 184.9241, '2002-20-09 13:35:54.593')
insert into #PreSaleSnapshot(CASID, CALID, UnitsHeld, SnapshotDate) 
Values(3972867, 10000008, 97.9251, '2003-05-03 12:16:37.420')
 
select * from #PreSaleSnapshot
 
update s
set 
PreSaleCASID = (select top 1 preCasB.CASID
from #PreSaleSnapshot preCasB
where s.CALID = preCasB.CALID
and s.EventDate <= preCasB.SnapshotDate
order by preCasB.SnapshotDate)
from 
#Sale s
 
 
select * from #Sale
 
drop Table #Sale
drop Table #PreSaleSnapshot

Select allOpen in new window