27,579
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([工号] int,[部门] nvarchar(23),[入职日期] Date,[最后工作日] Date)
Insert #T
select 1,N'行政部','2018/3/2','9999/12/31' union all
select 2,N'行政部','2018/3/3','2018/6/5' union all
select 3,N'行政部','2018/3/4','9999/12/31' union all
select 4,N'行政部','2018/3/5','9999/12/31' union all
select 5,N'行政部','2018/3/6','9999/12/31' union all
select 6,N'行政部','2018/3/7','9999/12/31' union all
select 7,N'行政部','2018/6/4','9999/12/31'
Go
--测试数据结束
DECLARE @sql VARCHAR(MAX)
SET @sql = 'select 部门'
SELECT @sql = @sql + ',sum(case when 入职日期<=''' + RTRIM(日期)
+ ''' and 最后工作日>=''' + RTRIM(日期)
+ ''' then 1 else 0 end)[' + CONVERT(VARCHAR(8),日期,112) + '在职人数]'
FROM ( SELECT DISTINCT
DATEADD(DAY, number, CONVERT(DATETIME, '2018-06-01')) AS 日期
FROM master..spt_values
WHERE type = 'P'
AND number
BETWEEN 0 AND DATEDIFF(DAY, '2018-06-01', DATEADD(MONTH, 1, '20