Solved

SQL Server Query Returns Wrong Results

Posted on 2010-08-31
13
233 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Hala75
Comment Utility
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
Comment Utility
This is what you want??
select country form table1
union
select country from table2
0
 

Author Comment

by:Hala75
Comment Utility
but i cant group by after union i got duplicate countries
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Hala75
Comment Utility
Can I group the Union results
0
 
LVL 11

Expert Comment

by:Larissa T
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now