Help With Date Ranges - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help With Date Ranges

Author

Topic

Vassago
Starting Member

33 Posts

Posted - 2006-12-06 : 22:49:07

Hello all,

I need some help sorting out a date range problem I have come across. I have two tables, one looks like this:

TABLE1:
Account, xdate, fromstat, tostat
1111 2006-10-11 00:00:00.000 CURR DELQ
1111 2006-10-18 00:00:00.000 DELQ NEXT
1111 2006-11-15 00:00:00.000 NEXT SOLD
2222 2006-10-16 00:00:00.000 CURR DELQ
2222 2006-10-31 00:00:00.000 DELQ SOLD

and another table that looks like this:

TABLE2:
Account, datadate
1111 2006-10-18 00:00:00.000
1111 2006-11-01 00:00:00.000
2222 2006-11-01 00:00:00.000

I need a query to check the datadate and then look at Table1 to find the cloaest match of the xdate, without going over and without reaching the datadate, so I can get the fromstat and tostat for each account. For example, in using the above data, I would get

Account, datadate, xdate, fromstat, tostat
1111 2006-10-18 00:00:00.000 2006-10-11 00:00:00.000 CURR DELQ
1111 2006-11-01 00:00:00.000 2006-10-18 00:00:00.000 DELQ NEXT
2222 2006-11-01 00:00:00.000 2006-10-31 00:00:00.000 DELQ SOLD

I have been racking my brain trying to find a way to make this work in sql. I know it's possible in VB, but I need it in SQL, can someone please give me some clues when dealing with date ranges?

Thanks!

Vassago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 01:41:35

[code]-- prepare test data
declare @table1 table (Account smallint, xdate smalldatetime, fromstat varchar(4), tostat varchar(4))

insert @table1
select 1111, '2006-10-11', 'CURR', 'DELQ' union all
select 1111, '2006-10-18', 'DELQ', 'NEXT' union all
select 1111, '2006-11-15', 'NEXT', 'SOLD' union all
select 2222, '2006-10-16', 'CURR', 'DELQ' union all
select 2222, '2006-10-31', 'DELQ', 'SOLD'

declare @table2 table (Account smallint, datadate smalldatetime)

insert @table2
select 1111, '2006-10-18' union all
select 1111, '2006-11-01' union all
select 2222, '2006-11-01'

-- do the work
select d.account,
d.datadate,
d.xdate,
t1.fromstat,
t1.tostat
from (
select t2.account,
t2.datadate,
(select top 1 t1.xdate from @table1 t1 where t1.account = t2.account and t1.xdate <> t2.datadate order by abs(datediff(day, t1.xdate, t2.datadate))) xdate
from @table2 t2
) d
inner join @table1 t1 on t1.account = d.account and t1.xdate = d.xdate[/code]
Peter Larsson
Helsingborg, Sweden

Vassago
Starting Member

33 Posts

Posted - 2006-12-07 : 19:37:52

Thank you so much! I never even though of doing anything close to this. Way too complex for me to figure out on my own.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 19:54:38

You're welcome.
Good luck!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources