Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CONCATENATE fields in JOIN Expand / Collapse
Author
Message
Posted Tuesday, December 22, 2009 4:46 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:29 AM
Points: 621, Visits: 1,073
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
Post #837835
Posted Tuesday, December 22, 2009 4:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:38 AM
Points: 6,908, Visits: 12,624
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #837837
Posted Tuesday, December 22, 2009 5:00 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:29 AM
Points: 621, Visits: 1,073
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
Post #837841
Posted Tuesday, December 22, 2009 5:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:38 AM
Points: 6,908, Visits: 12,624
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...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #837855
Posted Tuesday, December 22, 2009 6:18 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:29 AM
Points: 621, Visits: 1,073
Point taken, legs duly slapped :)
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
Post #837866
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse