JOIN Question

Question

Post reply

JOIN Question

Grinja

Hall of Fame

Points: 3590
More actions
November 25, 2010 at 7:00 am

#227198

Hi,
Need some help with a join.
In the code below there are two tables.
table tempAccount holds data for an account over a period of time. The second table holds the AccountOwner and the Date which s/he became the owner.
I would like to join the two tables to get all the account details and the person who was the owner at the time.
Any help is appreciated
G
CREATE TABLE #tempAccount
(
YearMonth int
, AccountID int
, AccountName varchar(20)
, AccountBalance Decimal (18,2)
)
INSERT INTO #tempAccount
SELECT 200801, 1, 'Blue', 500
UNION ALL
SELECT 200802, 1, 'Blue', 800
UNION ALL
SELECT 200803, 1, 'Blue', 1000
UNION ALL
SELECT 200804, 1, 'Blue', 1000
UNION ALL
SELECT 200805, 1, 'Blue', 1000
CREATE TABLE #tempAccountOwner
(
YearMonthStart INT
, AccountID INT
, OwnerID INT
, OwnerName VARCHAR(50)
)
INSERT INTO #tempAccountOwner
SELECT 200801, 1, 1, 'Bob'
UNION ALL
SELECT 200803, 1, 1, 'Fred'
SELECT
a.YearMonth
, a.AccountID
, a.AccountName
, a.AccountBalance
, b.OwnerName
FROM #tempAccount a
LEFT JOIN #tempAccountOwner b
ON a.AccountID = b.AccountID
--Cleanup:
DROP TABLE #tempAccount
DROP TABLE #tempAccountOwner

Viewing 6 posts - 1 through 5 (of 5 total)

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

tommyh SSCertifiable Points: 6302 More actions · Answer 1

Well if you really want the join you can change your join to

LEFT JOIN #tempAccountOwner b

ON a.AccountID = b.AccountID

AND b.YearMonthStart = (select top 1 b2.YearMonthStart

from #tempAccountOwner b2

where b2.AccountId = b.AccountId

and b2.YearMonthStart <= a.YearMonth

order by b2.YearMonthStart desc)

or if you can live without the join. Replace the entire join part with this

cross apply (select top 1 OwnerName

from #tempAccountOwner b2

where b2.AccountId = a.AccountId

and b2.YearMonthStart <= a.YearMonth

order by b2.YearMonthStart desc) b

/T

Grinja Hall of Fame Points: 3590 More actions · Answer 2

Thanks for this.

Is there a benefit to using the Cross Apply , I've never used it before ...

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

@grinja,

I just want to make sure I'm seeing the data correctly. It looks like you have account #1 called "BLUE" and it is owned by [font="Arial Black"]both [/font]"Fred" and "Bob". Is that correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grinja Hall of Fame Points: 3590 More actions · Answer 4

Hi Jef, that is correct.

The account belongs to Bob for period 200801 - 200802 and to Fred from 200803 onwards.

G

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Grinja (11/26/2010)
Hi Jef, that is correct.
The account belongs to Bob for period 200801 - 200802 and to Fred from 200803 onwards.
G

In that case, it looks like TommyH has the code that does the trick. Either way works.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)