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
PreSaleCASID = (select top 1 preCasB.CASID
from #PreSaleSnapshot preCasB
where s.CALID = preCasB.CALID
and s.EventDate <= preCasB.SnapshotDate
order by preCasB.SnapshotDate)
#Sale s
select * from #Sale
drop Table #Sale
drop Table #PreSaleSnapshot

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

>>s.EventDate <= preCasB.SnapshotDate

I believe that should be the other way around, since the sale comes after the snapshot.

s.EventDate >= preCasB.SnapshotDate
mylesevenAuthor Commented:
Thanks Gatorvip however when I try that I get these results:
SaleID      CALID       PreSaleCASID EventDate               UnitsHeld
----------- ----------- ------------ ----------------------- ---------------------------------------
600001659   10000008    569755       2000-06-16 13:53:16.000 0.0000
600001750   10000008    569755       2000-06-23 09:29:06.000 0.0000
990021176   10000008    569755       2002-09-26 16:12:48.060 0.0000
990051702   10000008    569755       2003-03-17 12:13:26.717 0.0000

ie the same PreSaleCASID for every SaleID
mylesevenAuthor Commented:
Done, see below...
update s
PreSaleCASID = (select	top 1 preCas.ClientAssetSnapshotID
from	#AllCALPreSaleSnaps preCas
where	s.ClientAssetLinkID = preCas.ClientAssetLinkID 
and preCas.SnapshotDate =(select max(SnapshotDate)
from #AllCALPreSaleSnaps 
where s.ClientAssetLinkID = ClientAssetLinkID 
and SnapshotDate < s.EventDate))
#AllCALSaleRecs s

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.