November 30, 2011 at 5:01 pm
I have two tables:
create table #A(aYr int, aCol int)
insert #A(aYr,aCol) values (2008, 2)
insert #A(aYr,aCol) values (2009, 5)
insert #A(aYr,aCol) values (2010, 9)
insert #A(aYr,aCol) values (2011, 8)
create table #b(bYr int, bCol int)
insert #A(bYr,bCol) values (2008, 3)
insert #A(bYr,bCol) values (2009, 4)
I want join two tables,
if aYr exists in bYr, then join two tables on aYr=bYr
if aYr>=max(bYr) then aYr=max(bYr)
the results will be
aYr bYr aCol bCol
2008 2008 2 3
2009 2009 5 4
2010 2009 9 4
2011 2009 8 4
how can I do this query. Thanks a lot.
December 1, 2011 at 4:16 am
Untested: -
CREATE TABLE #A (aYr INT, aCol INT)
INSERT #A (aYr, aCol) VALUES (2008, 2)
INSERT #A (aYr, aCol) VALUES (2009, 5)
INSERT #A (aYr, aCol) VALUES (2010, 9)
INSERT #A (aYr, aCol) VALUES (2011, 8)
CREATE TABLE #b (bYr INT, bCol INT)
INSERT #b (bYr, bCol) VALUES (2008, 3)
INSERT #b (bYr, bCol) VALUES (2009, 4)
SELECT aYr, bYr, aCol, CASE WHEN aYr = bYr
THEN bCol
ELSE MAX(bCol) OVER (PARTITION BY (SELECT NULL)) END AS bCol
FROM #A a
INNER JOIN #b b ON 1=1
WHERE aYr = bYr OR aYr = bYr+1
December 1, 2011 at 5:22 am
Slightly tested:
SELECT aYr, bYr, aCol, bCol
FROM #A a
CROSS APPLY (
SELECT MAX_bYr = MAX(bYr) FROM #b) x
INNER JOIN #b b
ON b.bYr = CASE WHEN a.aYr <= MAX_bYr THEN a.aYr ELSE MAX_bYr END
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 1, 2011 at 6:05 am
And another
SELECT aYr, bYr, aCol, bCol
FROM #A a
INNER JOIN (SELECT bYr, bCol, MAX(bYr) OVER (PARTITION BY (SELECT NULL)) AS maxbYr
FROM #b) b ON b.bYr = CASE WHEN a.aYr <= maxbYr
THEN a.aYr
ELSE maxbYr END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply