[协同工作]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));#tmpStuff(项目 varchar(20),月份 varchar(20),人数 int);#tmpStuff2(项目 varchar(20),月份 varchar(20),人数 int);#tmpStuff3(项目 varchar(20),月份 varchar(20),人数 int);declare @year as char(4)(6)declare @currentmonthcount as int –当月（含当月）以前入职到目前还在职人员数量declare @leavecount as 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 –定义离职总人数变量(2)while @i<=@month=right(@i+100,2)set @yearmonth =@year+right(@i+100,2)if(@yearmonth<=CONVERT(char(6),getdate(),112)) begin–统计当月（含当月）以前入职到目前还在职人员数量月份,count(A0183) as 数量A01156<>convert(char(6) ,A0141,112)<=@yearmonthgroup by A01156–当月（含当月）以前入职,当月之后离职人员数量月份,count(A0183) as 数量A01156<>’计划-仓储’and convert(char(6) ,A0141,112)<=@yearmonthand CONVERT(char(6),A0179,112)>@yearmonthgroup by A01156–非新员工当月离职统计insert into #tmpStuff2(项目,月份,人数)A01158 A01158 A01158 A01158 A01158 end 项目,@month as 月份,count(A0183) as 数量A01156<>’计划-仓储’and convert(char(6) ,A0141,112)<>@yearmonthand CONVERT(char(6),A0179,112)=@yearmonthgroup by A01158–新员工当月离职统计项目,@month as 月份,count(A0183) as 数量A01156<>’计划-仓储’and convert(char(6) ,A0141,112)=@yearmonthand CONVERT(char(6),A0179,112)=@yearmonth@currentmonthcount=–行转列：项目 月份 项目,月份,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–项目, 月份,sum(人数) as 人数 into #totalStuffLz from (select * from #tmpStuff3 * 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(项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月) –项目=项目=项目=项目,[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(项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月) –项目=项目=项目=项目,[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(项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月) –项目, [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(项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月) –,[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12] from UT_LZGoalwhere [year]=@yearinsert #Stuff(项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月) –项目,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

，怕仍是不能。于是他们比任何人都看的清楚，又比任何人都看的不确切。