[协同工作]EHR提取数据员工流失率分析

create table #Stuff(    项目 varchar(20),    一月 decimal(8,2) default (0) ,    二月 decimal(8,2) default (0) ,    三月 decimal(8,2) default (0),    四月 decimal(8,2) default (0),    五月 decimal(8,2) default (0),    六月 decimal(8,2) default (0),    七月 decimal(8,2) default (0),    八月 decimal(8,2) default (0),    九月 decimal(8,2) default (0),     十月 decimal(8,2) default (0),     十一月 decimal(8,2) default (0),     十二月 decimal(8,2) default (0));--班组在职人员统计临时表create table #tmpStuff(     项目 varchar(20),    月份 varchar(20),    人数 int);--新员工当月离职create table #tmpStuff2(     项目 varchar(20),    月份 varchar(20),    人数 int);--员工(不含当月)当月离职create table #tmpStuff3(     项目 varchar(20),    月份 varchar(20),    人数 int);declare @year as char(4)set @year='2015'declare @yearmonth as char(6)declare @currentmonthcount as int --当月（含当月）以前入职到目前还在职人员数量declare @leavecount as int --当月（含当月）以前入职,当月之后离职人员数量declare @i intdeclare @01 int,@02 int,@03 int,@04 int,@05 int,@06 int,@07 int,@08 int,@09 int,@10 int,@11 int,@12 int --定义总人数变量declare @01_lz int,@02_lz int,@03_lz int,@04_lz int,@05_lz int,@06_lz int,@07_lz int,@08_lz int,@09_lz int,@10_lz int,@11_lz int,@12_lz int --定义离职总人数变量set @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 #tmpStuff(项目,月份,人数)select A01156 as 项目,@month as 月份,count(A0183) as 数量          from A01 where A0183='电子'          and A01155='生产'         and A01156<>'计划-仓储'          and A0179 is null        and convert(char(6) ,A0141,112)<=@yearmonth        group by A01156        --当月（含当月）以前入职,当月之后离职人员数量        insert into #tmpStuff(项目,月份,人数)select A01156 as 项目,@month as 月份,count(A0183) as 数量         from A01 where A0183='电子'          and A01155='生产'         and A01156<>'计划-仓储'          and convert(char(6) ,A0141,112)<=@yearmonth        and CONVERT(char(6),A0179,112)>@yearmonth        group by A01156          --非新员工当月离职统计        insert into #tmpStuff2(项目,月份,人数)        select case when A01158 in ( '个人发展','个人离职','个人 辞职','个人辞职',NULL,'申请离职','紧急辞职','不适应','辞职')  then '辞职'        when A01158 is null  then  '辞职'        when A01158 in ('协议离职','合同期满')  then  '合同期满'        when A01158 in ('旷工自离','自动离职','自离')  then  '自离'        else A01158 end 项目,@month as 月份,count(A0183) as 数量         from A01 where A0183='电子'          and A01155='生产'         and A01156<>'计划-仓储'          and convert(char(6) ,A0141,112)<>@yearmonth        and CONVERT(char(6),A0179,112)=@yearmonth        group by A01158        --新员工当月离职统计        insert into #tmpStuff3(项目,月份,人数)select '新员工当月离职'  as 项目,@month as 月份,count(A0183) as 数量         from A01 where A0183='电子'          and A01155='生产'         and A01156<>'计划-仓储'          and convert(char(6) ,A0141,112)=@yearmonth        and CONVERT(char(6),A0179,112)=@yearmonth    end   set @i=@i+1   set @currentmonthcount=0   set @leavecount=0END--行转列：项目 月份 select '04.员工总人数' as 项目,月份,sum(人数) as 人数  into #totalStuff from #tmpStuff group by 月份--总人数存到变量select @01=[01], @02=[02], @03=[03], @04=[04], @05=[05], @06=[06], @07=[07], @08=[08], @09=[09], @10=[10],@11=[11],@12=[12]from #totalStuff pivot(max(人数) for 月份 in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) f--select '离职总人数' as 项目, 月份,sum(人数)  as 人数 into #totalStuffLz from (select * from #tmpStuff3 union all select * from #tmpStuff2) mgroup by m.月份--离职总人数存到变量select @01_lz=[01], @02_lz=[02], @03_lz=[03], @04_lz=[04], @05_lz=[05], @06_lz=[06], @07_lz=[07], @08_lz=[08], @09_lz=[09], @10_lz=[10],@11_lz=[11],@12_lz=[12]from #totalStuffLz pivot(max(人数) for 月份 in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) ginsert #Stuff(项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月) select * from #totalStuff pivot(max(人数) for 月份 in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) binsert #Stuff(项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月) --union allselect case when 项目='生产一线' then '01.生产一线'            when 项目='生产-背光' then '02.生产-背光'            when 项目='生产-临时' 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])) ainsert #Stuff(项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月) --union allselect  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])) cinsert #Stuff(项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月) --union allselect  '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 allselect '11.目标',[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12] from UT_LZGoalwhere [year]=@yearinsert #Stuff(项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月) --union allselect '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 #Stufforder by 项目drop table #Stuffdrop table #totalStuffdrop table #totalStuffLzdrop table #tmpStuffdrop table #tmpStuff2drop table #tmpStuff3