PSM0038-員級月度考核報表

一.登陸eHR3→人事管理,新增”員級月度考核統(tǒng)計報表”;二.主介面如下 查詢條件說明:1．產(chǎn)品處與部門兩查詢條件為2選1;2．以產(chǎn)品處進行查詢時默認為以人事行政組織中第2層級進行查詢;三.報表欄位如下(詳見附件)單位考核人數(shù)優(yōu)(15%)甲乙丙丁設(shè)定人數(shù)設(shè)定比例實際人數(shù)實際比例設(shè)定人數(shù)設(shè)定比例實際人數(shù)實際比例設(shè)定人數(shù)設(shè)定比例實際人數(shù)實際比例設(shè)定人數(shù)設(shè)定比例實際人數(shù)實際比例設(shè)定人數(shù)設(shè)定比例實際人數(shù)實際比例報表數(shù)據(jù)邏輯:1．考核人數(shù):為當前部門所有已考核完成人員總數(shù);2．設(shè)定比例:比例直接從配置表獲取;3．設(shè)定人數(shù):等於考核人數(shù)*設(shè)定比例;4．實際人數(shù):為已考核完成人員總當前等級總?cè)藬?shù);5．實際比例:等於實際人數(shù)÷考核人數(shù);附件PSM_SelectPSMEmpMonthlyReviewR_SPUSE[eHR3]GO/******Object:StoredProcedure[dbo].[PSM_SelectPSMEmpMonthlyReviewR_SP]ScriptDate:09/11/201320:03:28******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGO/*<Rev>1</Rev>*/--=============================================--Author:F2913108--Createdate:2013-09-11--Description:查詢月度考核數(shù)據(jù)--=============================================ALTERProcedure[dbo].[PSM_SelectPSMEmpMonthlyReviewR_SP]( @OrgQueryTypeINT=0,--0:處級1:部門 @OrgSystemIDVARCHAR(36)=NULL,--部門 @BeginDateVARCHAR(10)=NULL,--考核開始日期 @EndDateVARCHAR(10)=NULL--考核結(jié)束日期)ASBEGINSETNOCOUNTON --print@BeginDate--print@EndDateIF(ISNULL(@BeginDate,'')='')Select@BeginDate=Convert(varchar(10),GETDATE(),120)IF(ISNULL(@EndDate,'')='')Select@EndDate=Convert(varchar(10),GETDATE(),120)--printConvert(varchar(10),GETDATE(),120)--print@BeginDate--print@EndDate--returnIF(@OrgQueryType=0)--處級BEGIN SelectRowNumber,OrgSystemID,OrgName,FinalCount,cast(ROUND(FinalCount*InitRateAA,0)asint)asInitCountAA,cast(InitRateAA*100asint)asInitRateAA,SumCountAA,cast(ROUND(SumCountAA/FinalCount,2)*100asint)asSumCountAA,cast(ROUND(FinalCount*InitRateA,0)asint)asInitCountA,cast(InitRateA*100asint)asInitRateA,SumCountA,cast(ROUND(SumCountA/FinalCount,2)*100asint)asSumCountA,cast(ROUND(FinalCount*InitRateB,0)asint)asInitCountB,cast(InitRateB*100asint)asInitRateB,SumCountB,cast(ROUND(SumCountB/FinalCount,2)*100asint)asSumCountB,cast(ROUND(FinalCount*InitRateC,0)asint)asInitCountC,cast(InitRateC*100asint)asInitRateC,SumCountC,cast(ROUND(SumCountC/FinalCount,2)*100asint)asSumCountC,cast(ROUND(FinalCount*InitRateD,0)asint)asInitCountD,cast(InitRateD*100asint)asInitRateD,SumCountD,cast(ROUND(SumCountD/FinalCount,2)*100asint)asSumCountDfrom(SELECTROW_NUMBER()OVER(ORDERBYOrgName)ASRowNumber,OrgSystemID,OrgName,[0]asSumCountAA,[1]asSumCountA,[2]asSumCountB,[3]asSumCountC,[4]asSumCountD,--(ISNULL([0],0)+ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0))*10ASFinalCountISNULL([0],0)+ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)ASFinalCount,InitRateAA,InitRateA,InitRateB,InitRateC,InitRateDFROM(selectm.OrgSystemID,m.OrgName,m.SsessmentResult,COUNT(*)asSumCount,m.InitRateAA,m.InitRateA,m.InitRateB,m.InitRateC,m.InitRateDfrom(---Selectm1.OrgSystemID,m1.OrgName,m1.SsessmentResult,r.year,r.[0]asInitRateAA,r.[1]asInitRateA,r.[2]asInitRateB,r.[3]asInitRateC,r.[4]asInitRateDfromPSMEmpMonthlyReviewT(NOLOCK)m1leftJOIN(SELECT*FROM(Selectg.EmpRateGroupID,g.year,s.Value,R.RateFromPAMEmpRateGroupM(NOLOCK)gleftjoinPAMEmpScoreRateM(NOLOCK)rong.EmpRateGroupID=r.EmpRateGroupIDleftjoin(SelectValue,helpervaluefromE9Option(NOLOCK)wheretablename='PSMEmpMonthlyReviewT'andColName='SsessmentResult')assonr.EmpScoreRateName=s.helpervaluewhereg.GroupType=1)ASBPIVOT(MAX(Rate)FORvalueIN([0],[1],[2],[3],[4]))ASpvt)ASronSubstring(Convert(varchar(10),m1.CreateDt,120),1,4)=CONVERT(varchar(4),r.year) where ----比較組織 m1.OrgSystemIDin( SelectOrgSystemIDFromPSOrgTwhereparentDept= (SelectorgSystemIDfromPSOrgTwhereorgType=0andparentDeptisnullandorgCode='CMMSG')) --andISNULL(m1.SsessmentResult,'')<>'' andConvert(varchar(10),m1.CreateDt,120)>=@BeginDate andConvert(varchar(10),m1.CreateDt,120)<=@EndDate ---)mleftjoin(SelectValue,helpervaluefromE9Optionwheretablename='PSMEmpMonthlyReviewT'andColName='SsessmentResult')assONm.SsessmentResult=s.ValueGroupbym.OrgSystemID,m.OrgName,m.SsessmentResult,helpervalue,m.InitRateAA,m.InitRateA,m.InitRateB,m.InitRateC,m.InitRateD)ASBPIVOT(MAX(SumCount)FORSsessmentResultIN([0],[1],[2],[3],[4]))ASpvt)s ENDIF(@OrgQueryType=1)--部門BEGIN WITHorg(parentdept,orgsystemid)AS ( SELECTparentdept, orgsystemid FROMPSOrgT(NOLOCK) WHEREisAble=1 ANDorgType=0 and(ISNULL(@OrgSystemID,'')=''ORorgsystemid=@OrgSystemID) ANDclientID=0 UNIONALL SELECTe.parentdept, systemid FROMPSOrgTe INNERJOINorgd ONe.parentdept=systemid ANDe.isAble=1 WHEREclientID=0 ) SELECTorgsystemidinto#tempFROMorg select*from#temp --return ----------------- SelectRowNumber,OrgSystemID,OrgName,FinalCount,cast(ROUND(FinalCount*InitRateAA,0)asint)asInitCountAA,cast(InitRateAA*100asint)asInitRateAA,SumCountAA,cast(ROUND(SumCountAA/FinalCount,2)*100asint)asSumCountAA,cast(ROUND(FinalCount*InitRateA,0)asint)asInitCountA,cast(InitRateA*100asint)asInitRateA,SumCountA,cast(ROUND(SumCountA/FinalCount,2)*100asint)asSumCountA,cast(ROUND(FinalCount*InitRateB,0)asint)asInitCountB,cast(InitRateB*100asint)asInitRateB,SumCountB,cast(ROUND(SumCountB/FinalCount,2)*100asint)asSumCountB,cast(ROUND(FinalCount*InitRateC,0)asint)asInitCountC,cast(InitRateC*100asint)asInitRateC,SumCountC,cast(ROUND(SumCountC/FinalCount,2)*100asint)asSumCountC,cast(ROUND(FinalCount*InitRateD,0)asint)asInitCountD,cast(InitRateD*100asint)asInitRateD,SumCountD,cast(ROUND(SumCountD/FinalCount,2)*100asint)asSumCountDfrom(SELECTROW_NUMBER()OVER(ORDERBYOrgName)ASRowNumber,OrgSystemID,OrgName,[0]asSumCountAA,[1]asSumCountA,[2]asSumCountB,[3]asSumCountC,[4]asSumCountD,--(ISNULL([0],0)+ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0))*10ASFinalCountISNULL([0],0)+ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)ASFinalCount,InitRateAA,InitRateA,InitRateB,InitRateC,InitRateDFROM(selectm.OrgSystemID,m.OrgName,m.SsessmentResult,COUNT(*)asSumCount,m.InitRateAA,m.InitRateA,m.InitRateB,m.InitRateC,m.InitRateDfrom(---Selectm1.OrgSystemID,m1.OrgName,m1.SsessmentResult,r.year,r.[0]asInitRateAA,r.[1]asInitRateA,r.[2]asInitRateB,r.[3]asInitRateC,r.[4]asInitRateDfromPSMEmpMonthlyReviewT(NOLOCK)m1leftJOIN(SELECT*FROM(Selectg.EmpRateGroupID,g.year,s.Value,R.RateFromPAMEmpRateGroupM(NOLOCK)gleftjoinPAMEmpScoreRateM(NOLOCK)rong.EmpRateGroupID=r.EmpRateGroupIDleftjoin(SelectValue,helpervaluefromE9Option(NOLOCK)wheretablename='PSMEmpMonthlyReviewT'andColName='SsessmentResult')assonr.EmpScoreRateName=s.helpervaluewhereg.GroupType=1)ASBPIVOT(MAX(Rate)FOR