Hi All:
I am trying to generate a resutset which computes certain
financial values based on aggregation and then these values
are presented horizontally one after the another in columns.
The basic skeleton of the SQL Query is thus:
SELECT



FROM
(
 This derived table gets the avg of
(Assets_Liabilities_Confirmed)
SELECT avg(ReportField4) as Assets_Liabilities_Confirmed
WHERE ...
AND l.ReportField1="Confirmed by MSFS"
group by l.periodStartDate
,a.advisorLegalName
,f.fundLegalName
) derived_table_1
,
(
 This derived table gets the avg (Provided by 3rd Party)
SELECT avg(ReportField4) as Provided by 3rd Party
WHERE ...
AND l.ReportField1="third party"
group by l.periodStartDate
,a.advisorLegalName
,f.fundLegalName
) derived_table_2
WHERE (1=1)
AND derived_table_1.month=derived_table_2.month
AND
derived_table_1.advisorLegalName=derived_table_2.advisorLegalName
AND
derived_table_1.fundLegalName=derived_table_2.fundLegalName
The issue is: <<suppose derived_table_2>> returns no rows
which means, that there are no records for <<Provided by 3rd
party>> the join conditions
derived_table_1.XYZ=derived_table_2.XYZ fails and the entire
SQL Query resultset becomes <no rows returmed>.
So essentially, i am looking at handling the no rows
returned from derived_table_2 such that the other resutset
is not hampered i.e. derived_table_1 in this case.
Any pathways to solve this problem would be appreciated.
Kind regards,
~Mihir
select
dt_box.year
,dt_box.month
,dt_box.advisorLegalName
,dt_box.fundLegalName
,dt_S2.Assets_Liabilities_Confirmed
, dt_S3.Provided_by_Fund_Manager
FROM
(
select
datename(year, l.periodStartDate) year
,datename(month, l.periodStartDate) month
,a.advisorLegalName as advisorLegalName
,f.fundLegalName as fundLegalName
FROM
ODSAggregatesSet1 l
,FundSections fs
,Sections s
,fsid..Fund f
,fsid..Advisor a
WHERE (1=1)
AND s.sectionKey = fs.sectionKey
AND fs.fundId= l.fundId
AND s.sectionKey = l.sectionKey
AND f.fundId=fs.fundId
AND f.advisorKey=a.advisorKey
AND l.periodStartDate between "01Sep2009" AND "31Dec2009"
AND l.baseCurrency = "USD"
AND effectiveStartDate <= "31Dec2009"
AND effectiveEndDate >= "31Dec2009"
group by l.periodStartDate
,a.advisorLegalName
,f.fundLegalName
) dt_box
,
(
select datename(month, l.periodStartDate) month
,a.advisorLegalName as advisorLegalName
,f.fundLegalName as fundLegalName
,round(avg(ReportField4),3) as Assets_Liabilities_Confirmed
FROM
ODSAggregatesSet1 l
, FundSections fs
,Sections s
,fsid..Fund f
,fsid..Advisor a
WHERE (1=1)
AND s.sectionKey = fs.sectionKey
AND fs.fundId= l.fundId
AND s.sectionKey = l.sectionKey
AND f.fundId=fs.fundId
AND f.advisorKey=a.advisorKey
AND l.ReportField1="Confirmed by MSFS"
AND l.periodStartDate between "01Sep2009" AND "31Dec2009"
AND l.baseCurrency = "USD"
AND effectiveStartDate <= "31Dec2009"
AND effectiveEndDate >= "31Dec2009"
group by l.periodStartDate
,a.advisorLegalName
,f.fundLegalName
) dt_S2
,
(
select datename(month, l.periodStartDate) month
,a.advisorLegalName as advisorLegalName
,f.fundLegalName as fundLegalName
,round(avg(ReportField4),3) as Provided_by_Fund_Manager
FROM
ODSAggregatesSet1 l
, FundSections fs
,Sections s
,fsid..Fund f
,fsid..Advisor a
WHERE (1=1)
AND s.sectionKey = fs.sectionKey
AND fs.fundId= l.fundId
AND s.sectionKey = l.sectionKey
AND f.fundId=fs.fundId
AND f.advisorKey=a.advisorKey
AND l.ReportField1="Provided by Fund Manager"
AND l.periodStartDate between "01Sep2009" AND "31Dec2009"
AND l.baseCurrency = "USD"
AND effectiveStartDate <= "31Dec2009"
AND effectiveEndDate >= "31Dec2009"
group by l.periodStartDate
,a.advisorLegalName
,f.fundLegalName
) dt_S3
WHERE (1=1)
AND dt_box.month=dt_S2.month
AND dt_box.advisorLegalName=dt_S2.advisorLegalName
AND dt_box.fundLegalName=dt_S2.fundLegalName
AND dt_box.month=dt_S3.month
AND dt_box.advisorLegalName=dt_S3.advisorLegalName
AND dt_box.fundLegalName=dt_S3.fundLegalName
order by dt_box.month
,dt_box.advisorLegalName
,dt_box.fundLegalName
=======================================================================================