Solved

SQL Server Query Returns Wrong Results

Posted on 2010-08-31
13
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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