SQL Server Query Returns Wrong Results

sum((Equity Position.amount * (Equity Position.Rate/100)) + ((Funds Position.Amount *(Funds Position.rate/100) * (Funds by Country.Ration/100)))

Well, your query starts out by selecting from the Tbl_Equity_Position_Data and then it INNER JOINs to the additional data.  That means that only the Country entries from the Tbl_Equity_Position_Data are all you are going to get.
I'm not at all sure how you expect to map the Hong Kong data to much of anything.

Try this. You may need to change order of left joins and/or check all your calculations. I do not have all your data to test

Ideas here :
1) you do need left join as correctly pointed by 8080_Diver
2) isnull(n.Country,a.Country) -
        use country from FundsCountry table and when none use EquityPosition table
3) (isnull(r.Exposure,0)+isnull(n.fexposure,0))
          make sure you have isnull(value,0)) in all your expression, since 5+null = null
4) I changed group by
        looking on result set you expect I think you do want to see do group by country only

Select
      isnull(n.Country,a.Country) as country , sum( (isnull(r.Exposure,0)+isnull(n.fexposure,0))) as totalExposure
from Tbl_Equity_Position_Data a
left join (select Site_ID, country, Sum(CASE WHEN (MarketValue_USD_Amount_000s > 0) and (A1+A2+A3+C1+C4+C5+C6 >0)
                                                            THEN MarketValue_USD_Amount_000s*((A1+A2+A3+C1+C4+C5+C6)/100) ELSE 0 END)
                                                            as Exposure From Tbl_Equity_Position_Data group by site_ID, Country) as r on a.Site_ID = r.Site_ID and r.country=a.Country

left join (select L.Site_ID, k.Country, L.Fund_Name ,Sum(Case When (L.Amount_USD_000s >0)and (L.A1+L.A2+L.A3+L.C1+L.C4+L.C5+L.C6 >0)and(k.Ratio >0)
                                                                        Then L.Amount_USD_000s *((L.A1+L.A2+L.A3+L.C1+L.C4+L.C5+L.C6)/100)*(k.Ratio/100) else 0 end)
                                                                        as FExposure from
                                                                        Tbl_Funds_By_Country k,Tbl_Funds_Position L where k.site_ID = L.Site_ID and L.Fund_Name = k.Fund_Name group by L.Site_ID, k.Country, L.Fund_Name) as N on N.site_ID = a.site_ID

left join Tbl_Equity_Position_Data b on b.Date = (select max(d.Date) from Tbl_Equity_Position_Data d where d.Site_ID = b.Site_ID)
where a.Site_ID = 7
group by isnull(n.Country,a.Country)

I have tried the SQL above but still not showing all countries from both tables..
Ok I want to start Step by step... First If I have 2 Tables
Table 1 :
Countries
USA
UK
Hong Kong

Table 2:
Countries
Spain
Italy
UK
Hong Kong

What is the SQL to get
Countries
USA
UK
Hong Kong
Spain
Italy

Basicly all the countries in both tables?

This is what you want??
select country form table1
union
select country from table2

but i cant group by after union i got duplicate countries

Can I group the Union results

Here is select that returns all data from your sample.
You need to define how fund's country relates to to equity county


create table #EquityPosition (siteId int, dt datetime, country varchar(80), rate int, amount int)
--Site_ID   Date              Country       Rate        Amount
insert into #EquityPosition
select 7,'2010/08/30',     'Egypt',           50,           1000
union select 7,'2010/08/30',     'Lebanon',           20,           3000

create table #FundsPosition (siteId int, dt datetime, FundName varchar(80), rate int, amount int) --      2- Funds Position
insert into #FundsPosition
select 7, '2010/08/30','A',60,       3000

create table #FundCountry (siteId int, dt datetime, FundName varchar(80), country varchar(80), ratio int) --      3- Funds by Country
insert into #FundCountry
select         7,' 2010/08/30','A','Hong Kong',2
union select         7,'2010/08/30','A','Egypt',5
union select         7,'2010/08/30','A','USA',3

select e.country EquityCOuntry, c.country fundCountry,  e.rate, e.amount, c.fundName, c.ratio, f.*
 from #EquityPosition e
      join #FundsPosition f on e.siteid = f.siteID
      left join #FundCountry c on e.siteid =c.siteid and f.fundName = c.fundName

I got this code....
Results are correct but I need after union , Group by Country

Results from Code
Country         Amount
Egypt      500.000000
Lebanon      500.000000
Egypt      10.000000
Egypt      15.000000
Hong Kong      10.000000

Results Desired

Country         Amount
Egypt      525.000000
Lebanon      500.000000
Hong Kong      10.000000

Select a.Country , r.exposure from Tbl_Equity_Position_Data a
inner  join (select Site_ID, country, Sum(CASE WHEN (MarketValue_USD_Amount_000s > 0) and (A1+A2+A3+C1+C4+C5+C6 >0)
			THEN MarketValue_USD_Amount_000s*((A1+A2+A3+C1+C4+C5+C6)/100) ELSE 0 END)
			as Exposure From Tbl_Equity_Position_Data group by site_ID, Country) as r on a.Site_ID = r.Site_ID and r.country=a.Country

union all

Select 
      isnull(n.Country,a.Country) as country ,isnull(n.fexposure,0) 
from Tbl_Equity_Position_Data a
 inner join (select L.Site_ID, k.Country, L.Fund_Name ,Sum(Case When (L.Amount_USD_000s >0)and (L.A1+L.A2+L.A3+L.C1+L.C4+L.C5+L.C6 >0)and(k.Ratio >0)
             Then L.Amount_USD_000s *((L.A1+L.A2+L.A3+L.C1+L.C4+L.C5+L.C6)/100)*(k.Ratio/100) else 0 end)
             as FExposure from Tbl_Funds_By_Country k,Tbl_Funds_Position L where k.site_ID = L.Site_ID and L.Fund_Name = k.Fund_Name group by L.Site_ID, k.Country, L.Fund_Name) as N on N.site_ID = a.site_ID

inner  join Tbl_Equity_Position_Data b on b.Date = (select max(d.Date) from Tbl_Equity_Position_Data d where d.Site_ID = b.Site_ID)
where a.Site_ID = 7
group by isnull(n.Country,a.Country),isnull(n.fexposure,0)

Select allOpen in new window

Union eliminates duplicate rows
Run this on sample data from prior comment
select country from #EquityPosition
union
select country from #FundCountry

That Works I added Derived before Group by and added Sum instead of MAX

Thanks very much

Select Country, sum(Exposure)from (
Select a.Country , r.exposure from Tbl_Equity_Position_Data a
inner  join (select Site_ID, country, Sum(CASE WHEN (MarketValue_USD_Amount_000s > 0) and (A1+A2+A3+C1+C4+C5+C6 >0)
			THEN MarketValue_USD_Amount_000s*((A1+A2+A3+C1+C4+C5+C6)/100) ELSE 0 END)
			as Exposure From Tbl_Equity_Position_Data group by site_ID, Country) as r on a.Site_ID = r.Site_ID and r.country=a.Country

union all

Select 
      isnull(n.Country,a.Country) as country ,isnull(n.fexposure,0) 
from Tbl_Equity_Position_Data a
 inner join (select L.Site_ID, k.Country, L.Fund_Name ,Sum(Case When (L.Amount_USD_000s >0)and (L.A1+L.A2+L.A3+L.C1+L.C4+L.C5+L.C6 >0)and(k.Ratio >0)
             Then L.Amount_USD_000s *((L.A1+L.A2+L.A3+L.C1+L.C4+L.C5+L.C6)/100)*(k.Ratio/100) else 0 end)
             as FExposure from Tbl_Funds_By_Country k,Tbl_Funds_Position L where k.site_ID = L.Site_ID and L.Fund_Name = k.Fund_Name group by L.Site_ID, k.Country, L.Fund_Name) as N on N.site_ID = a.site_ID

inner  join Tbl_Equity_Position_Data b on b.Date = (select max(d.Date) from Tbl_Equity_Position_Data d where d.Site_ID = b.Site_ID)
where a.Site_ID = 7
group by isnull(n.Country,a.Country),isnull(n.fexposure,0))
Derived group by Country

Select allOpen in new window

Thank you