?
EHR提取数据员工流失率分析(二)
产-临时' then '03.生产-临时'
else '其他' end as 项目,
[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12]
from #tmpStuff pivot(max(人数) for 月份 in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) a
insert #Stuff(项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月)
--union all
select case when 项目='自离' then '06.自离'
when 项目='辞职' then '07.辞职'
when 项目='开除' then '08.开除'
else '09.辞退' end as 项目,
[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12]
from #tmpStuff2 pivot(max(人数) for 月份 in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) c
insert #Stuff(项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月)
--union all
select
'05.新员工当月离职' as 项目,
[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12]
from #tmpStuff3 pivot(max(人数) for 月份 in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) d
insert #Stuff(项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月)
--union all
select '11.目标',[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12] from UT_LZGoal
where [year]=@year
insert #Stuff(项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月)
--union all
select '10.总流动率' as 项目,
cast(round(@01_lz*1.0/@01,4) as numeric(5,4)) ,
cast(round(@02_lz*1.0/@02,4) as numeric(5,4)),
cast(round(@03_lz*1.0/@03,4) as numeric(5,4)),
cast(round(@04_lz*1.0/@04,4) as numeric(5,4)),
cast(round(@05_lz*1.0/@05,4) as numeric(5,4)),
cast(round(@06_lz*1.0/@06,4) as numeric(5,4)),
cast(round(@07_lz*1.0/@07,4) as numeric(5,4)),
cast(round(@08_lz*1.0/@08,4) as numeric(5,4)),
cast(round(@09_lz*1.0/@09,4) as numeric(5,4)),
cast(round(@10_lz*1.0/@10,4) as numeric(5,4)),
cast(round(@11_lz*1.0/@11,4) as numeric(5,4)),
cast(round(@12_lz*1.0/@12,4) as numeric(5,4))
select 项目,
isnull(一月,0) as 一月,
isnull(二月,0) as 二月,
isnull(三月,0) as 三月,
isnull(四月,0) as 四月,
isnull(五月,0) as 五月,
isnull(六月,0) as 六月,
isnull(七月,0) as 七月,
isnull(八月,0) as 八月,
isnull(九月,0) as 九月,
isnull(十月,0) as 十月,
isnull(十一月,0) as 十一月,
isnull(十二月,0) as 十二月
from #Stuff
order by 项目
drop table #Stuff
drop table #totalStuff
drop table #totalStuffLz
drop table #tmpStuff
drop table #tmpStuff2
drop table #tmpStuff3