sql - Full Outer Join returning too many rows -
what i'm trying achieve data set have set of holdings data against benchmark , show holdings either on or off benchmark. data set should this:
positiondate portfoliocode benchmark securitydesciption portfolioweight benchmarkweight 2017-03-31 port1 jpm sec1 1.00 1.5 2017-03-31 port1 jpm sec2 0.54 null 2017-03-31 port1 jpm sec3 null 0.5 my sql looks following full outer join can return weights both portfolio data , benchmark data depending on whether portfolio holds or not:
select lt.positiondate positiondate , lt.portfoliocode portfoliocode , gpi.indx_desc benchmark , i.iss_desc securitydescription , lt.baseccyeffectiveexposure / nullif(lt.totalportfoliomv_baseccy,0) portfolioweight , issindx.wght_eod benchmarkweight #lkthru lt inner join issue_dg on i.instr_id = lt.id_netik_instr_id inner join gp_bnchmrk_xref bxref -- portfolio benchmark crossreference on bxref.acct_id = lt.portfoliocode -- links portfolio , bxref.bnchmrk_type = 'primary' -- link reporting benchmark , (@enddate between bxref.start_tms , isnull(bxref.end_tms,@enddate)) -- ensures pick correct benchmark date reporting inner join gp_index gpi on gpi.indx_id = bxref.indx_id -- links gp_index pick correct benchmark full outer join iss_indx_cnstnt issindx -- join ensure have constituents of benchmark returned portfolio constituents on issindx.indx_instr_id = gpi.instr_id , issindx.cnstnt_instr_id = i.instr_id -- join constituents of portfolio , issindx.as_of_dte = @enddate however i'm not returning desired result set , seem getting every record iss_indx_cnsnt returned. must filters after on on full outer join or not possible i'm trying achieve? i'm trying avoid having put filtered data temp table , join or creating union , group by.
does have ideas i'm going wrong?
edit - included working example
declare @enddate datetime set @enddate = '2017-03-31' declare @port table ( portcode varchar(40) ) insert @port values ('port1') declare @lkthru table ( positiondate datetime , portfoliocode varchar(40) , instr_id varchar(40) , portfolioweight float ) insert @lkthru values('2017-03-31','port1','1',1.00) insert @lkthru values('2017-03-31','port1','2',0.54) declare @issue_dg table ( instr_id varchar(40) , securitydesc varchar(100) ) insert @issue_dg values ('1','sec1') insert @issue_dg values ('2','sec2') insert @issue_dg values ('3','sec3') insert @issue_dg values ('4','sec4') declare @gp_bnchrk_xref table ( portfoliocode varchar(40) , benchmarkid varchar(40) ) insert @gp_bnchrk_xref values ('port1','jpm1') declare @gpindex table ( benchmarkid varchar(40) , benchmarknme varchar(40) ) insert @gpindex values ('jpm1','jpm') declare @issueindexconst table ( benchmarkdate datetime , benchmarkid varchar(40) , const_id varchar(40) , benchmarkweight float ) insert @issueindexconst values ('2017-03-31','jpm1','1',1.5) insert @issueindexconst values ('2017-03-31','jpm1','3',0.5) insert @issueindexconst values ('2017-03-31','jpm1','4',0.5) insert @issueindexconst values ('2017-02-28','jpm1','1',1.5) insert @issueindexconst values ('2017-02-28','jpm1','3',0.5) insert @issueindexconst values ('2017-02-28','jpm1','4',0.5) select isnull(lt.positiondate,issindx.benchmarkdate) positiondate , isnull(lt.portfoliocode,bxref2.portfoliocode) portfoliocode , isnull(gpi.benchmarknme,gpi2.benchmarknme) benchmark , isnull(i.securitydesc,ib.securitydesc) securitydesc , lt.portfolioweight , issindx.benchmarkweight @gp_bnchrk_xref bxref inner join @lkthru lt on lt.portfoliocode = bxref.portfoliocode inner join @issue_dg on i.instr_id = lt.instr_id inner join @gpindex gpi on gpi.benchmarkid = bxref.benchmarkid full outer join @issueindexconst issindx on issindx.benchmarkid = gpi.benchmarkid , issindx.const_id = i.instr_id , issindx.benchmarkdate = @enddate left outer join @issue_dg ib on ib.instr_id = issindx.const_id left outer join @gpindex gpi2 on gpi2.benchmarkid = issindx.benchmarkid left outer join @gp_bnchrk_xref bxref2 on bxref2.benchmarkid = issindx.benchmarkid
Comments
Post a Comment