March 13, 2013 at 2:26 am
Create table Amount
(
YoA int ,
Currency char(3),
Amt decimal (9,2)
)
Create table Master
(
YoA int ,
Currency char(3),
)
Insert into Amount
select 2008, 'CAD' , 3400 UNION
select 2008, 'USD' , 400 UNION
select 2009, 'CAD' , 560 UNION
select 2010, 'USD' , 6750
insert into MAster
select 2008, 'CAD' UNION
select 2008, 'USD' UNION
select 2009, 'CAD' UNION
select 2009, 'USD' UNION
select 2010, 'CAD' UNION
select 2010, 'USD'
Required Output
2008, CAD, 3400
2008, USD, 400
2009, CAD, 560
2009, USD, 0.00
2010, CAD, 0.00
2010, USD, 6750
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 13, 2013 at 2:37 am
SELECT m.*, Amount = ISNULL(a.Amt,0)
FROM #MAster m
LEFT JOIN #Amount a
ON a.YoA = m.YoA AND a.Currency = m.Currency
ORDER BY m.YoA, m.Currency
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
March 13, 2013 at 3:03 am
select m.YoA,m.Currency,ISNULL(a.Amt,0)
from MasterTbl m
Left Outer Join
Amount as a
on (m.YoA=a.YoA AND m.Currency=a.Currency)
-- This Should be one of the possible solution ...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply