Outer join question - Pl help

Question

Outer join question - Pl help

Viewing 4 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Ray K SSC-Dedicated Points: 31172 More actions · Answer 1

Ray K (1/26/2010)
Okay -- for anyone trying to work on this, I've put together a quick 'n dirty test scenario.
Here's the sample data:create table #test1 (tdate date)
go
create table #test2 (fdate date)
go
insert into #test1 (tdate) values ('1/15/2010')
insert into #test1 (tdate) values ('1/13/2010')
insert into #test1 (tdate) values ('1/1/2010')
insert into #test1 (tdate) values ('1/11/2010')
insert into #test2 (fdate) values ('1/15/2010')
insert into #test2 (fdate) values ('1/1/2010')
insert into #test2 (fdate) values ('1/10/2010')
As I clarified earlier, the goal is to create a join that does this:
tdatefdate
2010-01-152010-01-15
2010-01-012010-01-01
2010-01-132010-01-10
2010-01-112010-01-10
I'm messing around with some ideas, but so far, have come up empty. Hopefully, multiple eyes will come up with some more ideas.

Here's where I'm going with this. Using this test data, I'm starting out with a very simple join that looks like this:

select tdate, fdate from #test1 t1

full outer join #test2 t2 on

t1.tdate >= t2.fdate

However, it doesn't give us the correct result. It joins all the dates that are equal to or greater than, not just the first one, like this:tdatefdate

2010-01-152010-01-15

2010-01-152010-01-01

2010-01-132010-01-01

2010-01-012010-01-01

2010-01-112010-01-01

2010-01-152010-01-10

2010-01-132010-01-10

2010-01-112010-01-10

I'm messing around with utilizations of MAX and SELECT TOP 1, but so far, no dice. I'll keep playing with this, but again, if any of you with more knowledge than me has any ideas . . .

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

Ray K (1/26/2010)
I'm messing around with utilizations of MAX and SELECT TOP 1, but so far, no dice. I'll keep playing with this, but again, if any of you with more knowledge than me has any ideas . . .

It's easy to get confused. You want the record with the max date less than or equal to the test date; but it's not the date from the record that you want, it's the rate, so you can't just select the max date. I've done this before. Some day I'll remember how I did it.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Venkata-433090 Old Hand Points: 366 More actions · Answer 3

Gents,

Many thanks for all your answers. I'll implement both strategies (row_number() & CTE) and let you know how it did.

K

SQLRNNR SSC Guru Points: 281344 More actions · Answer 4

Venkata, unless I am missing the boat on this one, I recently blogged about a similar scenario I ran into that required some date matching.

Check it out at:

http://jasonbrimhall.info/?p=193

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events