hi,this query works fine but for cases where there are ratefixdtes in swds and frdt, it brings back entries individually. i need to join these. Could somebody help me do this. Thanks!
select s1.RateFixDte, s.RateCode, s.intccy, (sum(s.prinamt)/1000000)as 'Total Refix'
from swds s join swdh h on s.DealNo = h.DealNo
join swds s1 on s1.DealNo = s.DealNo and s1.Seq = s.Seq and s1.SchdSeq = s.SchdSeq - 1 and s1.FixFloatInd = s.FixFloatInd
where s.SchdType <> 'V' and s.FixFloatInd = 'L' and h.Revdate is null and s1.ratefixdte>= '1 june 2008' and s1.ratefixdte< '30 june 2008' and s.ratecode='3mlibgb' and
h.ProdType in('SP','cs') and h.port in ('me') and h.activeind='y' and
h.Trad in ('me')and s.princcy in ('gbp','usd') and
rtrim(h.trad)+rtrim(s.princcy) not in ('meur', 'reur')
group by s1.RateFixDte,s.IntStrtDte, s.RateCode,s.intccy
Union
select t.ratefixdte , t.RateCode, t.ccy, (Sum(t.ccyamt) / 1000000) as 'Total Refix'
from opicslive.dbo.FRDt t where t.revdate is null and t.product='FRA' and t.Trad='Me' and
t.ratefixdte>= '1 june 2008' and t.ratecode= '3mlibgb'
Group by t.ratefixdte, t.RateCode, t.ccy
select s1.RateFixDte, s.RateCode, s.intccy, (sum(s.prinamt)/1000000)as 'Total Refix'
from swds s join swdh h on s.DealNo = h.DealNo
join swds s1 on s1.DealNo = s.DealNo and s1.Seq = s.Seq and s1.SchdSeq = s.SchdSeq - 1 and s1.FixFloatInd = s.FixFloatInd
where s.SchdType <> 'V' and s.FixFloatInd = 'L' and h.Revdate is null and s1.ratefixdte>= '1 june 2008' and s1.ratefixdte< '30 june 2008' and s.ratecode='3mlibgb' and
h.ProdType in('SP','cs') and h.port in ('me') and h.activeind='y' and
h.Trad in ('me')and s.princcy in ('gbp','usd') and
rtrim(h.trad)+rtrim(s.princcy) not in ('meur', 'reur')
group by s1.RateFixDte,s.IntStrtDte, s.RateCode,s.intccy
Union
select t.ratefixdte , t.RateCode, t.ccy, (Sum(t.ccyamt) / 1000000) as 'Total Refix'
from opicslive.dbo.FRDt t where t.revdate is null and t.product='FRA' and t.Trad='Me' and
t.ratefixdte>= '1 june 2008' and t.ratecode= '3mlibgb'
Group by t.ratefixdte, t.RateCode, t.ccy