sql server语言 - 按天分组，累计和

Question

我在MSSQL中有一个表，其结构如下:

PersonId
StartDate
EndDate

我需要能够显示表中日期范围内或给定日期的不同人数。

作为一个例子，我需要显示每天的总数，例如，如果我们在6月1日有2个条目，6月2日有3个条目，6月3日有1个条目，系统应该显示以下结果:

1st June: 2
2nd June: 5
3rd June: 6

但是，如果6月2日的条目中有一个结束日期是6月2日，那么6月3日的结果将只显示5。

有人能帮忙吗?

感谢<标题> 更新

这是我到目前为止似乎工作。有没有更好的解决方案，虽然我的解决方案只让我就业的数字。我还需要另一列的待业人员-待业人员意味着表中没有条目或日期不在之间且没有其他条目为待业人员。

CREATE TABLE #Temp(CountTotal int NOT NULL, CountDate datetime NOT NULL); 
DECLARE @StartDT DATETIME
SET @StartDT =  '2015-01-01 00:00:00'
WHILE @StartDT < '2015-08-31 00:00:00'
BEGIN
   INSERT INTO #Temp(CountTotal, CountDate)
   SELECT COUNT(DISTINCT PERSON.Id) AS CountTotal, @StartDT AS CountDate     FROM PERSON 
   INNER JOIN DATA_INPUT_CHANGE_LOG ON PERSON.DataInputTypeId = DATA_INPUT_CHANGE_LOG.DataInputTypeId AND PERSON.Id = DATA_INPUT_CHANGE_LOG.DataItemId 
   LEFT OUTER JOIN PERSON_EMPLOYMENT ON PERSON.Id = PERSON_EMPLOYMENT.PersonId 
   WHERE PERSON.Id > 0  AND DATA_INPUT_CHANGE_LOG.Hidden = '0' AND DATA_INPUT_CHANGE_LOG.Approved = '1' 
   AND ((PERSON_EMPLOYMENT.StartDate <= DATEADD(MONTH,1,@StartDT) AND PERSON_EMPLOYMENT.EndDate IS NULL) 

   OR (@StartDT BETWEEN PERSON_EMPLOYMENT.StartDate AND PERSON_EMPLOYMENT.EndDate) AND PERSON_EMPLOYMENT.EndDate IS NOT NULL)
   SET @StartDT = DATEADD(MONTH,1,@StartDT)
END
select * from #Temp
drop TABLE #Temp

Answer 1

您可以使用以下查询。cte部分是在开始日期和结束日期之间生成一组串行日期。

DECLARE @ViewStartDate DATETIME
DECLARE @ViewEndDate DATETIME
SET @ViewStartDate = '2015-01-01 00:00:00.000';
SET @ViewEndDate = '2015-02-25 00:00:00.000';
;WITH Dates([Date])
AS
(
    SELECT @ViewStartDate
    UNION ALL
    SELECT DATEADD(DAY, 1,Date)  
    FROM Dates
    WHERE DATEADD(DAY, 1,Date) <= @ViewEndDate
)
SELECT [Date], COUNT(*) 
FROM Dates
    LEFT JOIN PersonData ON Dates.Date >= PersonData.StartDate 
    AND Dates.Date <= PersonData.EndDate
GROUP By [Date]

• 将PersonData替换为您的表名

• 如果起始日期和结束日期列可以为空，则需要添加连接的附加条件

• 假设一个人在同一日期范围内只有一条记录

Answer 2

您可以通过创建每个开始日期为+1事件和结束日期为-1的数据来实现这一点，然后在此基础上计算运行总数。

例如，如果你的数据是这样的

PersonId   StartDate   EndDate
1          20150101    20150201
2          20150102    20150115
3          20150101    

首先创建一个如下所示的数据集:

EventDate   ChangeValue
20150101    +2
20150102    +1
20150115    -1
20150201    -1

如果你使用跑步总数，你会得到这个:

EventDate   Total
2015-01-01  2
2015-01-02  3
2015-01-15  2
2015-02-01  1

你可以这样得到它:

select
  p.eventdate,
  sum(p.changevalue) over (order by p.eventdate asc) as total
from
(
  select startdate as eventdate, sum(1) as changevalue from personnel group by startdate
  union all
  select enddate, sum(-1) from personnel where enddate is not null group by enddate
) p
order by p.eventdate asc

使用sum()实现窗口函数需要SQL Server 2012。如果您使用的是旧版本，您可以检查其他选项来运行总数。

我的例子在SQL提琴

如果你的日期没有任何事件，你也需要显示这些，那么最好的选择可能是为你需要的整个范围创建一个单独的日期表，例如1.1.2000 - 31.12.2099。

—Edit—

要获得特定日期的计数，可以使用相同的逻辑，但只需将当天的所有内容相加:

declare @eventdate date
set @eventdate = '20150117'
select
  sum(p.changevalue)
from
(
  select startdate as eventdate, 1 as changevalue from personnel 
  where startdate <= @eventdate
  union all
  select enddate, -1 from personnel 
  where enddate < @eventdate
) p

希望这是好的，不能测试，因为SQL提琴似乎不可用