1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
|
USE [Universe]
GO
/****** Object: StoredProcedure [dbo].[unvRepConstituentsAddition] Script Date: 24/09/2013 17:18:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[unvRepConstituentsAddition] @reviewDate date, @eco char(1) = null
AS BEGIN
-- srcFileName: unvUtil.sql
--
-- purpose: get the list of constituents added
--
create table #eco ( eco char(1) null )
if ( @eco is null )
begin
insert #eco (eco) values ('D')
insert #eco (eco) values ('E')
end
else
begin
insert #eco (eco) select @eco
end
select *
into #current
from mrkCompoMaster cm
join #eco e on (e.eco = SUBSTRING(cm.blockCode,2,1))
where cm.universeDate=@reviewdate
delete #current
from #current c
join unvCompoOut co on co.pricingDate=c.pricingdate and co.tradingItemId=c.tradingItemId
declare @prevuniversedate date
select @prevuniversedate=MAX(universedate)
from unvFrame f
where f.universeDate < @reviewdate
select *
into #previous
from mrkCompoMaster cm
join #eco e on (e.eco = SUBSTRING(cm.blockCode,2,1))
where universeDate=@prevuniversedate
delete #previous
from #previous p
join unvCompoOut co on co.pricingDate=p.pricingdate and co.tradingItemId=p.tradingItemId
delete #current
from #current c
join #previous p on c.scibetaId=p.sciBetaId
select c.universeDate, c.blockCode, SecId=sciBetaId, Company=companyName, Country=c.companyCountry, MIC, Ccy=cur.ISOCode, Action='Add', EffectiveDate=convert(date, dateadd(day,3,c.universeDate)), Comment='', Security=sec.securityName, isin, sedol
from
#current c
left outer join CIQ.dbo.ciqSecurity sec on sec.securityId=c.securityId
join CIQ.dbo.ciqCurrency cur on cur.currencyId=c.currencyId
order by effectiveDate desc, companyName asc
drop table #current, #previous, #eco
END ; |
Partager