SQL Server Query Returns Wrong Results

Hello Experts I have 3 Tables
1- Equity Position
Site_ID   Date              Country       Rate        Amount
7           30/08/2010     Egypt           50           1000
7           30/08/2010     Lebanon      20           3000

2- Funds Position
Funds Name   Site_ID  Date                    Rate    Amount
A                     7          30/08/2010          60       3000

3- Funds by Country
FundsNAme          Site_ID         Date                 Country                   Ratio
       
A                            7                 30/08/2010      Hong Kong                2
A                           7                  30/08/2010      Egypt                         5
A                           7                  30/08/2010       USA                         3


the main equation is sum((Equity Position.amount * (Equity Position.Rate/100)) + (Funds Position.Amount *(Funds Position.rate/100) * (Funds by Country.Ration/100) where Equity Position.amount .site_ID = Funds Position.Site_ID , Funds Position.FundsName =Funds by Country. FundsName and Funds Position.Site_ID =Funds by Country. Site_ID)

so The Results of the Query should be

Country                TotalExposure
Egypt                     590                
Lebanon               600
HongKong             36
USA                       54

What I am getting from Query is only the Countries in the Equity Position Table.....!!
I am  attaching my  code below

Your prompt guidance is highly appreciated

Select 
	a.Country ,  (r.Exposure+n.fexposure) as totalExposure 
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

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 a.Country, n.country,r.Exposure,n.fexposure

Open in new window

Hala75Asked:
Who is Participating?
 
Lara FConnect With a Mentor EACommented:
you can go group by after union
or you can put union into subselect


select country, max(rate), max(amount), max(ratio)
from (
select country, rate,amount , 0 ratio from #EquityPosition
union
select country,0 rate, 0 amount, ratio  from #FundCountry
) tbl
group by country
0
 
Hala75Author Commented:
sum((Equity Position.amount * (Equity Position.Rate/100)) + ((Funds Position.Amount *(Funds Position.rate/100) * (Funds by Country.Ration/100)))
0
 
8080_DiverCommented:
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.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Lara FEACommented:
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)
0
 
Hala75Author Commented:
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?

0
 
Lara FEACommented:
This is what you want??
select country form table1
union
select country from table2
0
 
Hala75Author Commented:
but i cant group by after union i got duplicate countries
0
 
Hala75Author Commented:
Can I group the Union results
0
 
Lara FEACommented:
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
0
 
Hala75Author Commented:
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)

Open in new window

0
 
Lara FEACommented:
Union eliminates duplicate rows
Run this on sample data from prior comment
select country from #EquityPosition
union
select country from #FundCountry
0
 
Hala75Author Commented:
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

Open in new window

0
 
Hala75Author Commented:
Thank you
0
All Courses

From novice to tech pro — start learning today.