[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

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

Open in new window

0
myleseven
Asked:
myleseven
  • 2
1 Solution
 
gatorvipCommented:
>>s.EventDate <= preCasB.SnapshotDate

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

s.EventDate >= preCasB.SnapshotDate
0
 
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
0
 
mylesevenAuthor Commented:
Done, see below...
update s
set 
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))
from 
#AllCALSaleRecs s

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now