Sql语句如何计算指定 月|季度|年 的离职人数、月初人数？

[e_dian_yun]

============================以下是源数据==============================
工号 入职日期 离职日期 离职类型
001 2017/1/10 2017/1/19 主动
002 2017/1/10
003 2017/1/10
004 2017/1/10
005 2017/1/10
006 2017/1/10 2017/2/19 被动
007 2017/1/10 2017/1/19 被动
008 2017/1/10
009 2017/1/10
010 2017/1/10 2017/2/19 被动
011 2017/1/10 2017/2/19 被动
012 2017/1/10
013 2017/1/10 2017/2/19 主动
014 2017/1/10 2017/2/19 被动
015 2017/2/10
016 2017/2/10
017 2017/2/10
018 2017/2/10 2017/3/22 主动
019 2017/2/10 2017/3/22 被动
020 2017/2/10
021 2017/2/10 2017/3/22 主动
022 2017/2/10 2017/3/22 主动


============================以下是效果============================
月份min 月|季度|年 月份max 离职人数 月初人数 本月入职 月末人数 主动 被动
2017/1/1 2017年1月 2017/1/31 离职日期落在min&max之间（含） 入职日期小于min，且离职日期为空或大于max 入职日期落在min&max之间（含） 月初人数+本期入职人数 主动落在月份的次数 被动落在月份的次数
2017/2/1 2017年2月 2017/2/28
2017/3/1 2017年3月 2017/3/31
2017/4/1 2017年4月 2017/4/30
2017/5/1 2017年5月 2017/5/31
2017/6/1 2017年6月 2017/6/30
2017/7/1 2017年7月 2017/7/31
2017/8/1 2017年8月 2017/8/31
2017/9/1 2017年9月 2017/9/30
2017/10/1 2017年10月 2017/10/31
2017/11/1 2017年11月 2017/11/30
2017/12/1 2017年12月 2017/12/31
2017/1/1 季度：1-3月 2017/3/31
2017/4/1 季度：4-6月 2017/6/30
2017/7/1 季度：7-9月 2017/9/30
2017/10/1 季度：10-12月 2017/12/31
2017/1/1 半年：1-6月 2017/6/30
2017/7/1 半年：7-12月 2017/12/31
2017/1/1 全年：1-12月 2017/12/31

[Q315054403]

根据入职、离职日期和所在月日期范围判断

[文盲老顾]

;with t as (
	select '001' as gh,convert(date,'2017-1-10') as fst,convert(date,'2017-1-19') as lst,'主动' as tp
	union all
	select '002','2017-1-10',null,null
	union all
	select '003','2017-1-10',null,null
	union all
	select '004','2017-1-10',null,null
	union all
	select '005','2017-1-10',null,null
	union all
	select '006' as gh,convert(date,'2017-1-10') as fst,convert(date,'2017-2-19') as lst,'被动' as tp
	union all
	select '007' as gh,convert(date,'2017-1-10') as fst,convert(date,'2017-1-19') as lst,'被动' as tp
	union all
	select '008','2017-1-10',null,null
	union all
	select '009','2017-1-10',null,null
	union all
	select '010' as gh,convert(date,'2017-1-10') as fst,convert(date,'2017-2-19') as lst,'被动' as tp
	union all
	select '011' as gh,convert(date,'2017-1-10') as fst,convert(date,'2017-2-19') as lst,'被动' as tp
	union all
	select '012','2017-1-10',null,null
	union all
	select '013' as gh,convert(date,'2017-1-10') as fst,convert(date,'2017-1-19') as lst,'主动' as tp
	union all
	select '014' as gh,convert(date,'2017-1-10') as fst,convert(date,'2017-2-19') as lst,'被动' as tp
	union all
	select '015','2017-2-10',null,null
	union all
	select '016','2017-2-10',null,null
	union all
	select '017','2017-2-10',null,null
	union all
	select '018' as gh,convert(date,'2017-2-10') as fst,convert(date,'2017-3-22') as lst,'主动' as tp
	union all
	select '019' as gh,convert(date,'2017-2-10') as fst,convert(date,'2017-3-22') as lst,'被动' as tp
	union all
	select '020','2017-2-10',null,null
	union all
	select '021' as gh,convert(date,'2017-2-10') as fst,convert(date,'2017-3-22') as lst,'主动' as tp
	union all
	select '022' as gh,convert(date,'2017-2-10') as fst,convert(date,'2017-3-22') as lst,'主动' as tp
),tt as (
	select dateadd(month,number,convert(date,'2017-1-1')) as m from master..spt_values where type='p' and number<datediff(month,'2017-1-1',getdate())
)
select * from tt a
cross apply(
	select m as 月份min
	,convert(varchar(max),year(m))+'年'+convert(varchar(max),month(m))+'月' as 月份
	,dateadd(d,-1,dateadd(month,1,m)) as 月份max
	,(select count(0) from t where datediff(month,m,lst)=0) as 离职人数
	,(select count(0) from t where datediff(d,m,fst)<=0 and (datediff(d,m,lst)>0 or lst is null)) as 月初人数
	,(select count(0) from t where datediff(month,m,fst)=0) as 本月入职
	,(select count(0) from t where datediff(month,m,fst)<=0 and (datediff(month,m,lst)>0 or lst is null)) as 月末人数
	,(select count(0) from t where datediff(month,m,lst)=0 and tp='主动') as 主动
	,(select count(0) from t where datediff(month,m,lst)=0 and tp='被动') as 被动
) b

[扬静宁]

可针对不同的结果进行分组查询，最后依据年度与月份进行关联计算与合并。 查询每月离职人数：

SELECT YEAR(E_DATE) 离职年度,MONTH(E_DATE) 离职月份,
CASE MONTH(E_DATE)
WHEN 1 THEN COUNT(E_DATE) WHEN 2 THEN COUNT(E_DATE) WHEN 3 THEN COUNT(E_DATE) WHEN 4 THEN COUNT(E_DATE) WHEN 5 THEN COUNT(E_DATE) WHEN 6 THEN COUNT(E_DATE)
WHEN 7 THEN COUNT(E_DATE) WHEN 8 THEN COUNT(E_DATE) WHEN 9 THEN COUNT(E_DATE) WHEN 10 THEN COUNT(E_DATE) WHEN 11 THEN COUNT(E_DATE) WHEN 12 THEN COUNT(E_DATE)
END [离职人数]
FROM TEST_B
GROUP BY E_DATE

查询每月入职人数：

SELECT YEAR(C_DATE) 入职年份,MONTH(C_DATE) 入职月份,
CASE MONTH(C_DATE)
WHEN 1 THEN COUNT(C_DATE) WHEN 2 THEN COUNT(C_DATE) WHEN 3 THEN COUNT(C_DATE) WHEN 4 THEN COUNT(C_DATE) WHEN 5 THEN COUNT(C_DATE) WHEN 6 THEN COUNT(C_DATE)
WHEN 7 THEN COUNT(C_DATE) WHEN 8 THEN COUNT(C_DATE) WHEN 9 THEN COUNT(C_DATE) WHEN 10 THEN COUNT(C_DATE) WHEN 11 THEN COUNT(C_DATE) WHEN 12 THEN COUNT(C_DATE)
END [本月入职]
FROM TEST_B
GROUP BY C_DATE

[扬静宁]

引用 2 楼 qq_25635235 的回复:

可针对不同的结果进行分组查询，最后依据年度与月份进行关联计算与合并。 查询每月离职人数：

SELECT YEAR(E_DATE) 离职年度,MONTH(E_DATE) 离职月份,
CASE MONTH(E_DATE)
WHEN 1 THEN COUNT(E_DATE) WHEN 2 THEN COUNT(E_DATE) WHEN 3 THEN COUNT(E_DATE) WHEN 4 THEN COUNT(E_DATE) WHEN 5 THEN COUNT(E_DATE) WHEN 6 THEN COUNT(E_DATE)
WHEN 7 THEN COUNT(E_DATE) WHEN 8 THEN COUNT(E_DATE) WHEN 9 THEN COUNT(E_DATE) WHEN 10 THEN COUNT(E_DATE) WHEN 11 THEN COUNT(E_DATE) WHEN 12 THEN COUNT(E_DATE)
END [离职人数]
FROM TEST_B
GROUP BY E_DATE

查询每月入职人数：

SELECT YEAR(C_DATE) 入职年份,MONTH(C_DATE) 入职月份,
CASE MONTH(C_DATE)
WHEN 1 THEN COUNT(C_DATE) WHEN 2 THEN COUNT(C_DATE) WHEN 3 THEN COUNT(C_DATE) WHEN 4 THEN COUNT(C_DATE) WHEN 5 THEN COUNT(C_DATE) WHEN 6 THEN COUNT(C_DATE)
WHEN 7 THEN COUNT(C_DATE) WHEN 8 THEN COUNT(C_DATE) WHEN 9 THEN COUNT(C_DATE) WHEN 10 THEN COUNT(C_DATE) WHEN 11 THEN COUNT(C_DATE) WHEN 12 THEN COUNT(C_DATE)
END [本月入职]
FROM TEST_B
GROUP BY C_DATE

依据上述两张表关联求出月初人数与月未人数，主动与被动就好解决了。