CONCATENATE fields in JOIN

Question

Post reply

CONCATENATE fields in JOIN

2Tall

SSChampion

Points: 14644
More actions
December 22, 2009 at 4:46 am

#136144

I need to concatenate 2 fields in table A in order to join to field in table b.
Table A:
Year int (displays as 2009, 2010 etc...)
Period int (1.....12)
Concatenate to = 200901
Table B
YearPeriod int (displays as ,200901,201001etc..)
JOIN
INNER JOIN table a ON tableB.YearPeriod = tableA.ConcatenatedField
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Why concatenate?

Since both are int, I'd use

SELECT Year * 100 + Period From TableA.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

2Tall SSChampion Points: 14644 More actions · Answer 2

Thanks for taking time to reply.

A friend has just come up with the answer as follows:

INNER JOIN (SELECT AccountingYear * 100 + AccountingPeriod AS YRPR,AccountingYear,AccountingPeriod,PeriodClosed,ROW_NUMBER() OVER(PARTITION BY AccountingYear * 100 + AccountingPeriod ORDER BY AccountingDate) AS RowNo FROM accountingCalendar) AS DTBL ON gli.GLYearPeriod = DTBL.YRPR AND DTBL.RowNo = 1

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

LutzM SSC Guru Points: 107049 More actions · Answer 3

Well, then your friend must have had a lot more information available than we did...

How else could he come up with Row_Number when there was nothing in your post indicating that you have duplicates and only need to get the first match?

Please don't compare apples and oranges...

Btw: this is a very nice example of how much an answer would vary based on the information provided...