实际工作中经常碰到占比，比如XX部门超领金额占总金额的占比，离职人员占总人数占比等等。一直都是存取变量，计算结果。今天尝试了下结果集直接进行四则运算-- ============================================= -- Author: <David Gong> -- Create date: <2015-9-23> -- Description: <材料溢领分析> -- ============================================= alter Proc [dbo].[UP_Excess_Material]( @year as char(4))asbegincreate table #tmp( 项目 varchar(20), 月份 varchar(20), 金额 decimal(16,2) default (0));--declare @year as char(4)--set @year='2015'declare @yearmonth as char(6)declare @01 decimal(16,2),@02 decimal(16,2),@03 decimal(16,2),@04 decimal(16,2),@05 decimal(16,2),@06 decimal(16,2),@07 decimal(16,2),@08 decimal(16,2),@09 decimal(16,2),@10 decimal(16,2),@11 decimal(16,2),@12 decimal(16,2) --定义变量declare @01_wc decimal(16,2),@02_wc decimal(16,2),@03_wc decimal(16,2),@04_wc int,@05_wc decimal(16,2),@06_wc decimal(16,2),@07_wc decimal(16,2),@08_wc decimal(16,2),@09_wc decimal(16,2),@10_wc decimal(16,2),@11_wc decimal(16,2),@12_wc decimal(16,2) --定义离职总人数变量declare @i intset @i=1declare @month char(2)while @i<=12BEGIN set @month=right(@i+100,2) set @yearmonth =@year+right(@i+100,2) if(@yearmonth<=CONVERT(char(6),getdate(),112)) begin --厂内领料总金额 insert into #tmp(项目,月份,金额) select '1.材料总金额' as 项目, @month 月份,sum(LA013) as 金额 from MOCTC inner join MOCTE ON TC001=TE001 AND TC002=TE002 inner join INVLA ON TE001=LA006 AND TE002=LA007 AND TE003=LA008 where TC008='54' AND TC001<>'5402' AND CONVERT(char(6),TC003,112)[email protected] --超领金额 union all select '2.溢领材料金额' as 项目, @month 月份,sum(LA013) as 金额 from MOCTC inner join MOCTE ON TC001=TE001 AND TC002=TE002 inner join INVLA ON TE001=LA006 AND TE002=LA007 AND TE003=LA008 where TC008='54' AND TC001='5402' AND CONVERT(char(6),TC003,112)[email protected] union all --超领按部门金额 select 项目,月份,sum(金额) as 金额 from ( select case when ME002 in ('BL车间','LCM车间') then '3.生产部金额' when ME002 ='开发部' then '4.开发部金额' when ME002 ='工程部' then '5.工程部金额' when ME002 ='采购部' then '6.采购部金额' else ME002 end as 项目, @month 月份,LA013 as 金额 from MOCTC inner join MOCTE ON TC001=TE001 AND TC002=TE002 inner join INVLA ON TE001=LA006 AND TE002=LA007 AND TE003=LA008 inner join CMSME ON TC021=ME001 where TC008='54' AND TC001='5402' AND CONVERT(char(6),TC003,112)[email protected]) K group by K.项目,K.月份 end set @[email protected]+1END---材料总金额select * into #tempallfrom #tmp pivot(max(金额) for 月份 in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) aselect 项目,[01] as 一月,[02] 二月,[03] 三月,[04] 四月,[05] 五月,[06] 六月,[07] 七月 ,[08] 八月 ,[09] 九月 ,[10] 十月,[11] 十一月,[12] 十二月 from #tempallunion all----两个结果集对应的字段进行四则运算select '7.总溢领率' as 项目 ,cast(round(a.[01]/b.[01],4) as numeric(5,4)),cast(round(a.[02]/b.[02],4) as numeric(5,4)),cast(round(a.[03]/b.[03],4) as numeric(5,4)),cast(round(a.[04]/b.[04],4) as numeric(5,4)),cast(round(a.[05]/b.[05],4) as numeric(5,4)),cast(round(a.[06]/b.[06],4) as numeric(5,4)),cast(round(a.[07]/b.[07],4) as numeric(5,4)),cast(round(a.[08]/b.[08],4) as numeric(5,4)),cast(round(a.[09]/b.[09],4) as numeric(5,4)),cast(round(a.[10]/b.[10],4) as numeric(5,4)),cast(round(a.[11]/b.[11],4) as numeric(5,4)),cast(round(a.[12]/b.[12],4) as numeric(5,4))from (select * from #tempall where 项目='1.材料总金额') as b,(select * from #tempall where 项目='2.溢领材料金额') as aunion allselect '8.生产部领率' as 项目 ,cast(round(a.[01]/b.[01],4) as numeric(5,4)),cast(round(a.[02]/b.[02],4) as numeric(5,4)),cast(round(a.[03]/b.[03],4) as numeric(5,4)),cast(round(a.[04]/b.[04],4) as numeric(5,4)),cast(round(a.[05]/b.[05],4) as numeric(5,4)),cast(round(a.[06]/b.[06],4) as numeric(5,4)),cast(round(a.[07]/b.[07],4) as numeric(5,4)),cast(round(a.[08]/b.[08],4) as numeric(5,4)),cast(round(a.[09]/b.[09],4) as numeric(5,4)),cast(round(a.[10]/b.[10],4) as numeric(5,4)),cast(round(a.[11]/b.[11],4) as numeric(5,4)),cast(round(a.[12]/b.[12],4) as numeric(5,4))from (select * from #tempall where 项目='1.材料总金额') as b,(select * from #tempall where 项目='3.生产部金额') as aunion allSELECT '9.'+[项目] ,[一月] ,[二月] ,[三月] ,[四月] ,[五月] ,[六月] ,[七月] ,[八月] ,[九月] ,[十月] ,[十一月] ,[十二月] FROM [OA].[dbo].[超领目标] where 年份=@yeardrop table #tmpdrop table #tempallend
版权声明：本文为博主原创文章，未经博主允许不得转载。