sql 语句查询计算 等问题

Question

现有表A 部门A1 人员状态（在职，离职）A3入职日期 A4离职日期

查询某月各部门月初在职人数、月末在职人数、入职人数、离职人数、人员入职率、人员离职率，其中人员入职率=入职人数/（（月初在职+月末在职）/2）、人员离职率=离职人数/（（月初在职+月末在职）/2）。
现显示如下
（部门名称、月初在职、月末在职、入职人数、离职人数、入职率、离职率）

Answer 1

create table test_20220327_a (A1 VARCHAR(100),A2 VARCHAR(10),A3 datetime,A4 datetime);
INSERT INTO test_20220327_a VALUES ('1','','2022-02-27 00:00:00','2022-03-27 00:00:00');
INSERT INTO test_20220327_a VALUES ('1','','2022-01-27 00:00:00','2022-02-27 00:00:00');
INSERT INTO test_20220327_a VALUES ('1','','2022-01-27 00:00:00','2022-02-27 00:00:00');
INSERT INTO test_20220327_a VALUES ('2','','2022-01-27 00:00:00','2022-02-27 00:00:00');
INSERT INTO test_20220327_a VALUES ('2','','2022-02-27 00:00:00','2022-03-27 00:00:00');
INSERT INTO test_20220327_a VALUES ('2','','2022-01-21 00:00:00','2022-05-27 00:00:00');

--假设查2022-02

select 部门,月初在职,月末在职,入职人数,离职人数,
round(入职人数/((月初在职+月末在职)/2)*100,2)  入职率,
round(离职人数/((月初在职+月末在职)/2)*100,2)  离职率 
from (
select A1 部门,
cast(count(case when '2022-02-01' between A3 and isnull(A4,'9999-12-31') then 1 end ) as NUMERIC) 月初在职,
cast(count(case when '2022-02-28' between A3 and isnull(A4,'9999-12-31') then 1 end ) as NUMERIC) 月末在职,
cast(count(case when A3 between '2022-02-01' and '2022-02-28' then 1 end) as NUMERIC) 入职人数,
cast(count(case when isnull(A4,'9999-12-31') between '2022-02-01' and '2022-02-28' then 1 end) as NUMERIC) 离职人数
from test_20220327_a
GROUP BY A1) b

如果只使用一个变量日期,那么可以根据这个变量来计算出对应的月初和月末是哪一天,把对应的计算表达式放到上面的sql中即可

 select dateadd(day,-day('2022-02-03')+1,'2022-02-03') firstday,
       dateadd(day,
               -day('2022-02-03'),
               dateadd(month,1,'2022-02-03')) lastday
   from t1

---

SQLSERVER有坑,除数为0时不会直接报错,而是输出不完整的数据,因此需要使用case when先判断一下除数是否为0

select 部门,月初在职,月末在职,入职人数,离职人数,
CASE WHEN 月初在职+月末在职 =0 THEN NULL ELSE round(入职人数/((月初在职+月末在职)/2)*100,2) END   入职率,
CASE WHEN 月初在职+月末在职 =0 THEN NULL ELSE round(离职人数/((月初在职+月末在职)/2)*100,2) END   离职率 
from (
select deptpath 部门,
cast(SUM(case when '2020-03-01' between a0144 and isnull(lzdate,'9999-12-31') then 1 ELSE 0 end ) as NUMERIC) 月初在职,
cast(SUM(case when '2020-03-31' between a0144 and isnull(lzdate,'9999-12-31') then 1 ELSE 0 end ) as NUMERIC) 月末在职,
cast(SUM(case when a0144 between '2020-03-01' and '2020-03-31' then 1 ELSE 0 end) as NUMERIC) 入职人数,
cast(SUM(case when isnull(lzdate,'9999-12-31') between '2020-03-01' and '2020-03-31' then 1 ELSE 0 end) as NUMERIC) 离职人数
from A01
GROUP BY deptpath) b