在几年的数据中查询查询某月员工的入、离职对照表怎么查，样式如下：
类型 在职人数 入职人数 入职比率 离职人数 离职比率
以类型分组
要查的某月以参数的形式限制 如：@YYDD=‘201201 '
dbo.PerEmployee 表名
EmpStateName 类型
InCumbency =1 时在职 =0 时不在职
ComeDate 入职日期
LeaverDate 离职日期
要考虑当月某类型在职人数为0的类型
无数据库！！！
- SQL code
DECLARE @YYDD Varchar(6)SET @YYDD='201201'Select EmpStateName, SUM(Case When InCumbency=1 Then 1 Else 0 End) ZaiZhiCount --在职人数没限制月份 , SUM(Case When CONVERT(Varchar(6), ComeDate, 112) = @YYDD Then 1 Else 0 End) RuZhiCount --入职人数 , CONVERT(Varchar(6),(CASE WHEN SUM(Case When CONVERT(Varchar(6), ComeDate, 112) = @YYDD Then 1 Else 0 End)=0 THEN 0 ELSE (CONVERT(Varchar(6),SUM(Case When CONVERT(Varchar(6), ComeDate, 112) = @YYDD Then 1 Else 0 End) *10000/(SUM(Case When InCumbency=1 Then 1 Else 0 End)*100))) END))+'%' RuZhiRate --入职比例 , SUM(Case When CONVERT(Varchar(6), LeaveDate, 112) = @YYDD Then 1 Else 0 End) LiZhiCount --离职人数 , CONVERT(Varchar(6),(CASE WHEN SUM(Case When CONVERT(Varchar(6), LeaveDate, 112) = @YYDD And InCumbency=0 Then 1 Else 0 End)=0 THEN 0 ELSE (CONVERT(Varchar(6),SUM(Case When CONVERT(Varchar(6), LeaveDate,112) = @YYDD Then 1 Else 0 End) *10000/(SUM(Case When InCumbency=1 Then 1 Else 0 End)*100))) END))+'%' LiZhiRate --离职比例 From PerEmployee Group By EmpStateName/*EmpStateName ZaiZhiCount RuZhiCount RuZhiRate LiZhiCount LiZhiRate正式工 4663 129 2% 267 5%NULL 249 0 0% 0 0%试用工 0 0 0% 0 0%实习生 69 0 0% 0 0%临时工 12 0 0% 0 0%*/
问题如下：①当前百分比保留2为小数的形式显示，四舍五入
②[email protected]
③将上面的格式添加一行，如下显示：
EmpStateName ZaiZhiCount RuZhiCount RuZhiRate LiZhiCount LiZhiRate
正式工 4663 129 2.77% 267 5.73%
NULL 249 0 0.00% 0 0.00%
试用工 0 0 0.00% 0 0.00%
实习生 69 0 0.00% 0 0.00%
临时工 12 0 0.00% 0 0.00%
合计：
感觉自己写的太复杂求高手指点下，谢谢！
------解决方案--------------------
：①当前百分比保留2为小数的形式显示，四舍五入
cast(xx as desc(18,2))
[email protected]
where 条件后闲置
③将上面的格式添加一行，如下显示：
GROUP BY WITH ROLLUP
------解决方案--------------------
- SQL code
DECLARE @YYDD VARCHAR(6)SET @YYDD = CONVERT(VARCHAR(6), GETDATE(), 112)SELECT EmpStateName, SUM(CASE WHEN InCumbency = 1 THEN 1 ELSE 0 END) ZaiZhiCount --在职人数没限制月份 , SUM(CASE WHEN CONVERT(VARCHAR(6), ComeDate, 112) = @YYDD THEN 1 ELSE 0 END) RuZhiCount --入职人数 , LTRIM(CONVERT(DECIMAL(9,2), ( CASE WHEN SUM(CASE WHEN CONVERT(VARCHAR(6), ComeDate, 112) = @YYDD THEN 1 ELSE 0 END) = 0 THEN 0 ELSE ( CONVERT(VARCHAR(6), SUM(CASE WHEN CONVERT(VARCHAR(6), ComeDate, 112) = @YYDD THEN 1 ELSE 0 END) * 100.0 / ( SUM(CASE WHEN InCumbency = 1 THEN 1 ELSE 0 END) )) ) END ))) + '%' RuZhiRate --入职比例 , SUM(CASE WHEN CONVERT(VARCHAR(6), LeaveDate, 112) = @YYDD THEN 1 ELSE 0 END) LiZhiCount --离职人数 , CONVERT(VARCHAR(6), ( CASE WHEN SUM(CASE WHEN CONVERT(VARCHAR(6), LeaveDate, 112) = @YYDD AND InCumbency = 0 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE ( CONVERT(VARCHAR(6), SUM(CASE WHEN CONVERT(VARCHAR(6), LeaveDate, 112) = @YYDD THEN 1 ELSE 0 END) * 10000 / ( SUM(CASE WHEN InCumbency = 1 THEN 1 ELSE 0 END) * 100 )) ) END )) + '%' LiZhiRate --离职比例 FROM PerEmployee--WHERE CONVERT(VARCHAR(6), ComeDate, 112)=CONVERT(VARCHAR(6), GETDATE(), 112)GROUP BY EmpStateNameWITH ROLLUP