22,209
社区成员
发帖
与我相关
我的任务
分享
Select EmpStateName, SUM(Case When InCumbency=1 Then 1 Else 0 End) ZaiZhiCount --在职人数
, SUM(Case When CONVERT(Varchar(6), ComeDate, 112) = @YYDD And InCumbency=1 Then 1 Else 0 End) RuZhiCount --入职人数
, SUM(Case When CONVERT(Varchar(6), ComeDate, 112) = @YYDD And InCumbency=1 Then 1 Else 0 End)/COUNT(1) RuZhiRate --入职比例
, SUM(Case When CONVERT(Varchar(6), LeaverDate, 112) = @YYDD And InCumbency=0 Then 1 Else 0 End) LiZhiCount --离职人数
, SUM(Case When CONVERT(Varchar(6), LeaverDate, 112) = @YYDD And InCumbency=0 Then 1 Else 0 End)/COUNT(1) LiZhiRate --离职比例
From PerEmployee
Group By EmpStateName
Select EmpStateName, SUM(Case When InCumbency=1 Then 1 Else 0 End) RuZhiCount --入职人数
, SUM(Case When InCumbency=1 Then 1 Else 0 End)/COUNT(1) RuZhiRate --入职比例
, SUM(Case When InCumbency=0 Then 1 Else 0 End) LiZhiCount --离职人数
, SUM(Case When InCumbency=0 Then 1 Else 0 End)/COUNT(1) LiZhiRate --离职比例
From PerEmployee
Where CONVERT(Varchar(6), GETDATE(), 112) = @YYDD
Group By EmpStateName