sql server代码求助

[emmahyt]

USE [aci_invest]
GO
/****** Object:
StoredProcedure [corfi].[pSelectPrefData]
Script Date: 11/01/2018 9:49:10 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



SET NOCOUNT ON;



-- Insert statements for procedure here




Select


i.issuer_name as issuer,

f.fundcode as Portfolio,

f.fund_name_en as Portname,

ice.class3_code,

i.issue_description as issue,

i.coupon_div_currency,

ia.coupon_dividend,

i.maturity_date_short,

i.maturity_date_long,

i.cusip as cusip,

i.private_placement,

ice.pf_class_level5 as PfClasslvl5,

ice.pf_class_level6 as indu,

ice.real_ftse_industry_sub_group,

p.share_par_value as par_value,

p.amortized_cost,

p.amortized_yield_at_cost,

p.local_book_value,

p.yield_at_cost,

p.base_market_price,

p.fx_rate,

p.base_mv_noaccint,

ia.yield_to_maturity,

p.local_accint,

p.local_mv_accint,

ia.modified_duration,

p.effective_date as as_of_date,

ice.pf_class_level1,

ice.pf_class_level2,

ice.pf_class_level3,

ice.pf_class_level4,

ice.pf_class_level4,

ice.pf_class_level5,

ice.pf_class_level7,

ice.pf_class_level6,

ice.real_ftse_industry_sub_group,

ice.fair_value_class_code,

ir.rating_ftse_tmx_alpha,

i.issuer_code,

i.parent_issuer_code,

i.issuer_name_en as issuer_name,

i.parent_issuer_name_en as parent_issuer,

ia.average_life_date,

ir.rating_blend10_alpha as Blend10,





--i.issuer_code as issuer_id,



--i.parent_issuer_code as parent_issuer_id,
case


when pf_class_level6='Provincial Guaranteed and Non-Guaranteed' then 'Provincial'

when pf_class_level6='Financial Products' then 'Financial'

when pf_class_level6='Federal Government' then 'Federal'

when pf_class_level6='Industrial Products' then 'Industrial'

else pf_class_level6

end as Sectors








from


main.vw_position p

join main.vw_fund f on p.effective_date=f.effective_date and p.fundcode=f.fundcode

join main.vw_instrument i on p.effective_date=i.effective_date and p.instrument_id=i.instrument_id

join main.vw_instrument_analytic ia on p.effective_date=ia.effective_date and p.instrument_id=ia.instrument_id

join main.vw_instrument_class_en ice on p.effective_date=ice.effective_date and p.instrument_id=ice.instrument_id

left join main.vw_instrument_rating ir on p.effective_date=ir.effective_date and p.instrument_id=ir.instrument_id

where


p.effective_date = '2018-01-10'

and (p.fundcode = 'A1015' OR p.fundcode = 'A12351')

and p.lt_flag = 'SL'

现在的代码是这样 出来的结果如图一

现在要求像图二这样 在图一的表格加一列 p.fundcode = 'A1015'时，每格子＝本身的base_mv_noaccint/ p.fundcode = 'A1015'的base_mv_noaccint总和 p.fundcode＝'A12351'也是同样 相当于求每个格子占所对应fundcode总和的比重

各位大神可以帮我看下怎么写么。。 感谢！！！

[道素]

增加一列

p.base_mv_noaccint/sum(p.base_mv_noaccint)over(partition by p.fundcode)

Select

i.issuer_name as issuer,
f.fundcode as Portfolio,
f.fund_name_en as Portname,
ice.class3_code,
i.issue_description as issue,
i.coupon_div_currency,
ia.coupon_dividend,
i.maturity_date_short,
i.maturity_date_long,
i.cusip as cusip,
i.private_placement,
ice.pf_class_level5 as PfClasslvl5,
ice.pf_class_level6 as indu,
ice.real_ftse_industry_sub_group,
p.share_par_value as par_value,
p.amortized_cost,
p.amortized_yield_at_cost,
p.local_book_value,
p.yield_at_cost,
p.base_market_price,
p.fx_rate,
p.base_mv_noaccint,
ia.yield_to_maturity,
p.local_accint,
p.local_mv_accint,
ia.modified_duration,
p.effective_date as as_of_date,
ice.pf_class_level1,
ice.pf_class_level2,
ice.pf_class_level3,
ice.pf_class_level4,
ice.pf_class_level4,
ice.pf_class_level5,
ice.pf_class_level7,
ice.pf_class_level6,
ice.real_ftse_industry_sub_group,
ice.fair_value_class_code,
ir.rating_ftse_tmx_alpha,
i.issuer_code,
i.parent_issuer_code,
i.issuer_name_en as issuer_name,
i.parent_issuer_name_en as parent_issuer,
ia.average_life_date,
ir.rating_blend10_alpha as Blend10,

--i.issuer_code as issuer_id,
--i.parent_issuer_code as parent_issuer_id,
case
when pf_class_level6='Provincial Guaranteed and Non-Guaranteed' then 'Provincial'
when pf_class_level6='Financial Products' then 'Financial'
when pf_class_level6='Federal Government' then 'Federal'
when pf_class_level6='Industrial Products' then 'Industrial'
else pf_class_level6
end as Sectors

p.base_mv_noaccint/sum(p.base_mv_noaccint)over(partition by p.fundcode)


from

main.vw_position p
join main.vw_fund f on p.effective_date=f.effective_date and p.fundcode=f.fundcode
join main.vw_instrument i on p.effective_date=i.effective_date and p.instrument_id=i.instrument_id
join main.vw_instrument_analytic ia on p.effective_date=ia.effective_date and p.instrument_id=ia.instrument_id
join main.vw_instrument_class_en ice on p.effective_date=ice.effective_date and p.instrument_id=ice.instrument_id
left join main.vw_instrument_rating ir on p.effective_date=ir.effective_date and p.instrument_id=ir.instrument_id
where

p.effective_date = '2018-01-10'
and (p.fundcode = 'A1015' OR p.fundcode = 'A12351')
and p.lt_flag = 'SL'

[emmahyt]

但是我还需要的是除的是每个值不同fundcode的不同的和诶。。

[emmahyt]

如果我想增加一列 让i.maturity_date_short 这个日期 比如显示的2029-06-18 减去 今天的日期 得出相差年数 四舍五入这样 能教教我怎么写嘛