create procedure ccyt_CP_fx_new
(
@xmbh varchar(20),--
@zrzx varchar(60),
@yf varchar(10),
@kmbhstr varchar(50),
@kmbhstrd varchar(50),
@fzbh varchar(50),
@d_jf varchar(50),
@d_df varchar(50),
@isall int ,
@hslx int
)
as
begin
declare @stryear varchar(20)
set @stryear=substring(rtrim(ltrim(@yf)),1,4)
declare @strxmbh varchar(20)
set @strxmbh=rtrim(ltrim(@xmbh)) + @stryear
declare @sql varchar(8000)
--dw
if @hslx =1
begin
set @sql ='SELECT WLDWDM AS XMDM,WLDWMC AS XMMC,LEN(LTRIM(RTRIM(WLDWDM ))) AS XMCD INTO #DHG_XMLB FROM XT_DWWL_'+@strxmbh
end
--gr
if @hslx =2
begin
set @sql ='SELECT WLDWDM AS XMDM,WLDWMC AS XMMC,LEN(LTRIM(RTRIM(WLDWDM ))) AS XMCD INTO #DHG_XMLB FROM XT_DWWL_'+@strxmbh
end
--bm
if @hslx =3
begin
set @sql ='SELECT WLDWDM AS XMDM,WLDWMC AS XMMC,LEN(LTRIM(RTRIM(WLDWDM ))) AS XMCD INTO #DHG_XMLB FROM XT_DWWL_'+@strxmbh
end
--xm
if @hslx =4
begin
set @sql ='SELECT WLDWDM AS XMDM,WLDWMC AS XMMC,LEN(LTRIM(RTRIM(WLDWDM ))) AS XMCD INTO #DHG_XMLB FROM XT_DWWL_'+@strxmbh
end
--xj
if @hslx =5
begin
set @sql='select xmdm,xmmc,LEN(LTRIM(RTRIM(xmdm))) AS XMCD into #dhg_hslb from xt_xmfp_'+ ltrim(@strxmbh)+' where xmdm like''' + ltrim(rtrim(@fzbh))+'%'' and xmlx=''3'' '
end
--cp
if @hslx =6
begin
set @sql='select xmdm,xmmc,LEN(LTRIM(RTRIM(xmdm))) AS XMCD into #dhg_hslb from xt_xmfp_'+ ltrim(@strxmbh)+' where xmdm like''' + ltrim(rtrim(@fzbh))+'%'' and xmlx=''2'' '
end
--cb
if @hslx =7
begin
set @sql='select xmdm,xmmc,LEN(LTRIM(RTRIM(xmdm))) AS XMCD into #dhg_hslb from xt_xmfp_'+ ltrim(@strxmbh)+' where xmdm like''' + ltrim(rtrim(@fzbh))+'%'' and xmlx=''1'' '
end
select @sql
set @sql='select a.xmdm as ''辅助代码'',a.xmmc as ''辅助名称'',b.df as ''收入''
,(case when
(select sum(y.'+ ltrim(rtrim(@d_df))+') from xt_fzb_'+ ltrim(@strxmbh)+' as y where
y.kmbh like''' + rtrim(ltrim(@kmbhstrd)) + '%''
and y.zrzx=''' +ltrim(@zrzx) +'''
and y.yf=''' + ltrim(@yf) + '''
and y.cpzxdm in' + ltrim(rtrim(@fzbh))+') =0
then 0 else convert(decimal(9,3),100*b.df/(select sum(m.'+ ltrim(rtrim(@d_df))+') from xt_fzb_'+ ltrim(@strxmbh)+' as m where
m.kmbh like''' + rtrim(ltrim(@kmbhstrd)) + ' %''
and m.zrzx=''' +ltrim(@zrzx) +'''
and m.yf=''' + ltrim(@yf) + '''
and m.cpzxdm in' + ltrim(rtrim(@fzbh)) + '))
end
) as ''收入比例(%)'' ,
a.jf as ''成本'',(
case
when
(select sum(z.'+ ltrim(@d_jf)+') from xt_fzb_'+ ltrim(@strxmbh)+' as z where
z.kmbh like''' + rtrim(ltrim(@kmbhstr)) + '%''
and z.zrzx=''' +ltrim(@zrzx) +'''
and z.yf=''' + ltrim(@yf) + '''
and z.cpzxdm in' + ltrim(rtrim(@fzbh))+') =0
then 0
else
convert(decimal(9,3),100*a.jf/(select sum(x.'+ ltrim(@d_jf)+') from xt_fzb_'+ ltrim(@strxmbh)+' as x where
x.kmbh like''' + rtrim(ltrim(@kmbhstr)) + '%''
and x.zrzx=''' +ltrim(@zrzx) +'''
and x.yf=''' + ltrim(@yf) + '''
and x.cpzxdm in' + ltrim(rtrim(@fzbh))+'))
end
)
as ''成本比例(%)''
,(b.df-a.jf) as ''差额''
from (
select sum('+ ltrim(rtrim(@d_jf))+') as jf,xmdm,xmmc
from (select xmdm,xmmc from
xt_xmfp_'+ ltrim(@strxmbh)+' where xmdm in' + ltrim(rtrim(@fzbh))+'
and xmlx=''2'') as c
left join
(select '+ ltrim(rtrim(@d_jf))+',cpzxdm
from xt_fzb_'+ ltrim(@strxmbh)+'
where
kmbh like''' + rtrim(ltrim(@kmbhstr)) + ' %''
and zrzx=''' +ltrim(@zrzx) +'''
and yf=''' + ltrim(@yf) + '''
) as d
on c.xmdm=d.cpzxdm
group by xmdm,xmmc
) as a,
(
select sum('+ ltrim(rtrim(@d_df))+') as df,xmdm,xmmc
from (select xmdm,xmmc from
xt_xmfp_'+ ltrim(@strxmbh)+' where xmdm in' + ltrim(rtrim(@fzbh))+'
and xmlx=''2'') as c
left join
(select '+ ltrim(rtrim(@d_df))+',cpzxdm
from xt_fzb_'+ ltrim(@strxmbh)+'
where
kmbh like''' + rtrim(ltrim(@kmbhstrd)) + ' %''
and zrzx=''' +ltrim(@zrzx) +'''
and yf=''' + ltrim(@yf) + '''
) as d
on c.xmdm=d.cpzxdm
group by xmdm,xmmc
)
as b
where a.xmdm=b.xmdm '
select @sql
set @sql=ltrim(rtrim(@sql))
-- exec(@sql)
end
/*
drop procedure ccyt_CP_fx_new
exec ccyt_CP_fx_new '200083106',
'21011324',
'200501',
'5401','51010001',
'125009'
,'BNJFFS','BNDFFS' ,0,6
select * from xt_dwwl_2000831062005
select * from xt_bmk_2000831062005
select * from xt_grwl_2000831062005
select * from xt_xmk_2000831062005
select * from xt_xmfp_2000831062005 where xmlx='2' --cp
select * from xt_xmfp_2000831062005 where xmlx='3' --xj
select * from xt_xmfp_2000831062005 where xmlx='1' --cb
select distinct dqbh as txmdm,dqbh as xmdm,dqmc as xmmc,LEN(LTRIM(RTRIM(dqbh))) as xmcd ,'0' as mxbz from xt_dwwl_2000831062005 union all
SELECT (dqbh +WLDWDM) as txmdm, WLDWDM AS XMDM,WLDWMC AS XMMC,LEN(LTRIM(RTRIM(WLDWDM ))) AS XMCD,'1' as mxbz FROM xt_dwwl_2000831062005
SELECT DISTINCT LEN(LTRIM(RTRIM(WLDWDM ))) FROM xt_dwwl_2000831062005
select xmdm,xmmc,LEN(LTRIM(RTRIM(xmdm))) AS XMCD into #dhg_xmlb from xt_xmfp_2000831062005 where xmdm like'125%' and xmlx='2'
*/