Solved

SQL Server Query Returns Wrong Results

Posted on 2010-08-31
13
237 Views
Last Modified: 2012-06-22
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

0
Comment
Question by:Hala75
  • 7
  • 5
13 Comments
 

Author Comment

by:Hala75
ID: 33568569
sum((Equity Position.amount * (Equity Position.Rate/100)) + ((Funds Position.Amount *(Funds Position.rate/100) * (Funds by Country.Ration/100)))
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 33569081
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
 
LVL 11

Expert Comment

by:Larissa T
ID: 33569287
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

Author Comment

by:Hala75
ID: 33575798
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
 
LVL 11

Expert Comment

by:Larissa T
ID: 33576442
This is what you want??
select country form table1
union
select country from table2
0
 

Author Comment

by:Hala75
ID: 33576635
but i cant group by after union i got duplicate countries
0
 

Author Comment

by:Hala75
ID: 33576639
Can I group the Union results
0
 
LVL 11

Expert Comment

by:Larissa T
ID: 33576720
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
 

Author Comment

by:Hala75
ID: 33576753
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
 
LVL 11

Expert Comment

by:Larissa T
ID: 33576759
Union eliminates duplicate rows
Run this on sample data from prior comment
select country from #EquityPosition
union
select country from #FundCountry
0
 
LVL 11

Accepted Solution

by:
Larissa T earned 500 total points
ID: 33576838
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
 

Author Comment

by:Hala75
ID: 33576956
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
 

Author Closing Comment

by:Hala75
ID: 33576967
Thank you
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question