求助统计各月在职人数语句

[peruna]

如图，员工离职时间在当月的 依然算他当月在职，离职时间为空的代表还未离职
图下面是我预期的结果，只需取到当年1月到当前月，求助下各位大神这个语句要怎么写。

[RINK_1]

if OBJECT_ID(N'TEMPDB..#T') IS NOT NULL
drop table #T
GO

CREATE TABLE #T
(STAFF_ID VARCHAR(10),
 START_DATE DATETIME,
 END_DATE DATETIME)

INSERT INTO #T
SELECT 'C','2017/1/1','2017/1/31' UNION ALL
SELECT 'D','2017/3/1','2017/5/31' UNION ALL
SELECT 'E','2017/5/1',NULL UNION ALL
SELECT 'F','2017/8/1',NULL
GO

WITH CTE
AS
(SELECT CAST(A.number+1 AS VARCHAR)+'月' AS [MONTH],
SUM(CASE WHEN DATEADD(MONTH,NUMBER,'2017-01-01') BETWEEN START_DATE AND ISNULL(END_DATE,GETDATE()) THEN 1 ELSE 0 END) AS AMOUNT
FROM master..spt_values A
JOIN #T B ON 1=1
WHERE TYPE='P'
AND number<=11
GROUP BY A.number+1
)

SELECT * FROM CTE
PIVOT (SUM(AMOUNT) FOR MONTH IN([1月],[2月],[3月],[4月],[5月],[6月],[7月],[8月],[9月],[10月],[11月],[12月])) AS B

[peruna]

引用 1 楼 RINK_1 的回复:

if OBJECT_ID(N'TEMPDB..#T') IS NOT NULL
drop table #T
GO

CREATE TABLE #T
(STAFF_ID VARCHAR(10),
 START_DATE DATETIME,
 END_DATE DATETIME)

INSERT INTO #T
SELECT 'C','2017/1/1','2017/1/31' UNION ALL
SELECT 'D','2017/3/1','2017/5/31' UNION ALL
SELECT 'E','2017/5/1',NULL UNION ALL
SELECT 'F','2017/8/1',NULL
GO

WITH CTE
AS
(SELECT CAST(A.number+1 AS VARCHAR)+'月' AS [MONTH],
SUM(CASE WHEN DATEADD(MONTH,NUMBER,'2017-01-01') BETWEEN START_DATE AND ISNULL(END_DATE,GETDATE()) THEN 1 ELSE 0 END) AS AMOUNT
FROM master..spt_values A
JOIN #T B ON 1=1
WHERE TYPE='P'
AND number<=11
GROUP BY A.number+1
)

SELECT * FROM CTE
PIVOT (SUM(AMOUNT) FOR MONTH IN([1月],[2月],[3月],[4月],[5月],[6月],[7月],[8月],[9月],[10月],[11月],[12月])) AS B

你好，谢谢你的回复，你这个语句我大致明白了，现在有一个新的问题，员工可能来自不同的部门，打比方，其中C,D来自AA部门，EF来自BB部门， 预期结果要按部门在分组合计，请问下这个要怎么加？

[xmniemaosheng]

那你需要一个历史记录表， 会存在调部门的情况

[peruna]

引用 3 楼 u010376948 的回复:

那你需要一个历史记录表， 会存在调部门的情况

这个表是有的，这个情况我考虑到了，这里暂时就不考虑这个情况

[RINK_1]

引用 2 楼 peruna 的回复:

[quote=引用 1 楼 RINK_1 的回复:]

if OBJECT_ID(N'TEMPDB..#T') IS NOT NULL
drop table #T
GO

CREATE TABLE #T
(STAFF_ID VARCHAR(10),
 START_DATE DATETIME,
 END_DATE DATETIME)

INSERT INTO #T
SELECT 'C','2017/1/1','2017/1/31' UNION ALL
SELECT 'D','2017/3/1','2017/5/31' UNION ALL
SELECT 'E','2017/5/1',NULL UNION ALL
SELECT 'F','2017/8/1',NULL
GO

WITH CTE
AS
(SELECT CAST(A.number+1 AS VARCHAR)+'月' AS [MONTH],
SUM(CASE WHEN DATEADD(MONTH,NUMBER,'2017-01-01') BETWEEN START_DATE AND ISNULL(END_DATE,GETDATE()) THEN 1 ELSE 0 END) AS AMOUNT
FROM master..spt_values A
JOIN #T B ON 1=1
WHERE TYPE='P'
AND number<=11
GROUP BY A.number+1
)

SELECT * FROM CTE
PIVOT (SUM(AMOUNT) FOR MONTH IN([1月],[2月],[3月],[4月],[5月],[6月],[7月],[8月],[9月],[10月],[11月],[12月])) AS B

你好，谢谢你的回复，你这个语句我大致明白了，现在有一个新的问题，员工可能来自不同的部门，打比方，其中C,D来自AA部门，EF来自BB部门， 预期结果要按部门在分组合计，请问下这个要怎么加？[/quote]

if OBJECT_ID(N'TEMPDB..#T') IS NOT NULL
drop table #T
GO
 
CREATE TABLE #T
(STAFF_ID VARCHAR(10),
 START_DATE DATETIME,
 END_DATE DATETIME,
 DEPART_ID VARCHAR(10))
 
INSERT INTO #T
SELECT 'C','2017/1/1','2017/1/31','001' UNION ALL
SELECT 'D','2017/3/1','2017/5/31','002' UNION ALL
SELECT 'E','2017/5/1',NULL,'001' UNION ALL
SELECT 'F','2017/8/1',NULL,'001'
GO
 
WITH CTE
AS
(SELECT CAST(A.number+1 AS VARCHAR)+'月' AS [MONTH],
SUM(CASE WHEN DATEADD(MONTH,NUMBER,'2017-01-01') BETWEEN START_DATE AND ISNULL(END_DATE,GETDATE()) THEN 1 ELSE 0 END) AS AMOUNT,
DEPART_ID
FROM master..spt_values A
JOIN #T B ON 1=1
WHERE TYPE='P'
AND number<=11
GROUP BY A.number+1,DEPART_ID
)
 
SELECT DEPART_ID,
[1月],[2月],[3月],[4月],[5月],[6月],[7月],[8月],[9月],[10月],[11月],[12月]
FROM CTE
PIVOT (SUM(AMOUNT) FOR MONTH IN([1月],[2月],[3月],[4月],[5月],[6月],[7月],[8月],[9月],[10月],[11月],[12月])) AS B

[peruna]

引用 5 楼 RINK_1 的回复:

[quote=引用 2 楼 peruna 的回复:] [quote=引用 1 楼 RINK_1 的回复:]

if OBJECT_ID(N'TEMPDB..#T') IS NOT NULL
drop table #T
GO

CREATE TABLE #T
(STAFF_ID VARCHAR(10),
 START_DATE DATETIME,
 END_DATE DATETIME)

INSERT INTO #T
SELECT 'C','2017/1/1','2017/1/31' UNION ALL
SELECT 'D','2017/3/1','2017/5/31' UNION ALL
SELECT 'E','2017/5/1',NULL UNION ALL
SELECT 'F','2017/8/1',NULL
GO

WITH CTE
AS
(SELECT CAST(A.number+1 AS VARCHAR)+'月' AS [MONTH],
SUM(CASE WHEN DATEADD(MONTH,NUMBER,'2017-01-01') BETWEEN START_DATE AND ISNULL(END_DATE,GETDATE()) THEN 1 ELSE 0 END) AS AMOUNT
FROM master..spt_values A
JOIN #T B ON 1=1
WHERE TYPE='P'
AND number<=11
GROUP BY A.number+1
)

SELECT * FROM CTE
PIVOT (SUM(AMOUNT) FOR MONTH IN([1月],[2月],[3月],[4月],[5月],[6月],[7月],[8月],[9月],[10月],[11月],[12月])) AS B

你好，谢谢你的回复，你这个语句我大致明白了，现在有一个新的问题，员工可能来自不同的部门，打比方，其中C,D来自AA部门，EF来自BB部门， 预期结果要按部门在分组合计，请问下这个要怎么加？[/quote]

if OBJECT_ID(N'TEMPDB..#T') IS NOT NULL
drop table #T
GO
 
CREATE TABLE #T
(STAFF_ID VARCHAR(10),
 START_DATE DATETIME,
 END_DATE DATETIME,
 DEPART_ID VARCHAR(10))
 
INSERT INTO #T
SELECT 'C','2017/1/1','2017/1/31','001' UNION ALL
SELECT 'D','2017/3/1','2017/5/31','002' UNION ALL
SELECT 'E','2017/5/1',NULL,'001' UNION ALL
SELECT 'F','2017/8/1',NULL,'001'
GO
 
WITH CTE
AS
(SELECT CAST(A.number+1 AS VARCHAR)+'月' AS [MONTH],
SUM(CASE WHEN DATEADD(MONTH,NUMBER,'2017-01-01') BETWEEN START_DATE AND ISNULL(END_DATE,GETDATE()) THEN 1 ELSE 0 END) AS AMOUNT,
DEPART_ID
FROM master..spt_values A
JOIN #T B ON 1=1
WHERE TYPE='P'
AND number<=11
GROUP BY A.number+1,DEPART_ID
)
 
SELECT DEPART_ID,
[1月],[2月],[3月],[4月],[5月],[6月],[7月],[8月],[9月],[10月],[11月],[12月]
FROM CTE
PIVOT (SUM(AMOUNT) FOR MONTH IN([1月],[2月],[3月],[4月],[5月],[6月],[7月],[8月],[9月],[10月],[11月],[12月])) AS B

[/quote] 非常感谢，学习了！