find smallest date2 where date2>date1

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

Transact-SQL (2000)

find smallest date2 where date2>date1

Author

Topic

Base
Starting Member

3 Posts

Posted - 2005-07-21 : 09:05:58

Hi All,

Struggling with the following challenge:

I have 2 large tables, each containing a column of the dateTime type. What I need to do is:
for each date1 in table1
find the smallest date2 from table2
that is larger than date1.

So (simplified example):

table1.date1
1
2
3
3
3
4
5
6
6
7
9

table2.date2
3
5
6
8

The required result would be:
1 - 3
2 - 3
3 - 5
3 - 5
3 - 5
4 - 5
5 - 6
6 - 8
6 - 8
7 - 8
9 - NULL

The problem is that these tables can become very very large, so a full-blown scan of table2 for every value in table1 would become very costly.
Anyone with a smart idea?

Thnx!

SuQQeL

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-21 : 09:21:16

[code]
select t1.date1, min(t2.date2)
from table1 t1
join table2 t2 on t2.date1 > t1.date2
group by t1.date1
[/code]

Go with the flow & have fun! Else fight the flow

Base
Starting Member

3 Posts

Posted - 2005-07-21 : 09:46:57

Hi Spirit,

Thanks for your speedy reply, but unfortunately your suggestion doesn't do the trick for me (tried it myself already...). Table1 has over 50k records and table2 close to 10k records, so executing the query as proposed takes several minutes (not sure how many, aborted it after 3 minutes on a Xeon processor)

Thnx!

SuQQeL

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-21 : 09:55:55

If performance is an issue, you really need to post actual table structures with the current indexes, relations, and primary keys.

- Jeff

Base
Starting Member

3 Posts

Posted - 2005-07-22 : 04:27:18

It's been solved!!!

I also posted this issue in several other forums and one of them pointed me in the right direction.

I've been working with JOIN's for a couple of weeks in a row and therefore I automatically started to tackle this one with a JOIN as well.
A reply in the DatabaseJournals forum suggested to use a subquery in the column definitions:

select t1.date1, (select min(t2.date2) from t2 where t2.date2 > t1.date1)
from t1

This shortened the query time from over 3 minutes to just 15 seconds.

Thanks for your efforts!

SuQQeL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources