SQL中的日期范围交集拆分

Question

我有一个SQL Server 2005数据库，其中包含一个名为Memberships的表。

表结构如下：

PersonID int, Surname nvarchar(30), FirstName nvarchar(30), Description nvarchar(100), StartDate datetime, EndDate datetime

我目前正在开发一个网格功能，其中显示了按人员分解的成员资格。其中一个要求是在日期范围相交处拆分会员行。 相交必须由姓和名字限定，即仅在具有相同姓氏和名字的会员记录中发生拆分。

有人有任何想法如何编写存储过程以返回具有上述分解的结果集吗？

- Matthew Pierce

---

你的设计如何处理具有相同名字/姓氏的多个成员？你提供的样本数据可能涉及到三个名为John Smith的不同人，这并非不可能。 - Ed Harper

这是一个很有价值的观点，我已经编辑了我的问题以反映这种可能性。事实上，我确实为每个人存储了一个ID，但在我写这个问题的时候，我没有考虑到重复的姓名。感谢您的反馈。 - Matthew Pierce

有一个PersonID - 我会完全忽略名称部分，直到最终输出。 - MartW

好的，为了举例说明，我会将其保留在问题中。 - Matthew Pierce

Answer 1

您将会遇到的问题是，随着数据集的增长，使用TSQL解决此问题的解决方案将无法很好地扩展。下面使用一系列临时表来解决问题。它使用一个数字表将每个日期范围条目拆分为其各自的天数。这是不可伸缩的原因之一，主要是由于您的开放范围NULL值，它们似乎是无限的，因此您必须替换成远期的固定日期，以限制转换范围的长度为可行的时间。通过构建一个包含适当索引的日期表或日历表，您可以更快地构建每天的优化呈现，从而可能看到更好的性能。

一旦范围被拆分，就使用XML PATH合并描述，以便范围系列中的每一天都列出了所有描述。通过PersonID和Date进行行编号，可以使用两个NOT EXISTS检查找到每个范围的第一行和最后一行，以查找先前不存在匹配PersonID和Description集的行或下一行不存在匹配PersonID和Description集的情况。

然后使用ROW_NUMBER重新编号结果集，以便它们可以配对构建最终结果。

/*
SET DATEFORMAT dmy
USE tempdb;
GO
CREATE TABLE Schedule
( PersonID int, 
 Surname nvarchar(30), 
 FirstName nvarchar(30), 
 Description nvarchar(100), 
 StartDate datetime, 
 EndDate datetime)
GO
INSERT INTO Schedule VALUES (18, 'Smith', 'John', 'Poker Club', '01/01/2009', NULL)
INSERT INTO Schedule VALUES (18, 'Smith', 'John', 'Library', '05/01/2009', '18/01/2009')
INSERT INTO Schedule VALUES (18, 'Smith', 'John', 'Gym', '10/01/2009', '28/01/2009')
INSERT INTO Schedule VALUES (26, 'Adams', 'Jane', 'Pilates', '03/01/2009', '16/02/2009')
GO

*/

SELECT 
 PersonID, 
 Description, 
 theDate
INTO #SplitRanges
FROM Schedule, (SELECT DATEADD(dd, number, '01/01/2008') AS theDate
    FROM master..spt_values
    WHERE type = N'P') AS DayTab
WHERE theDate >= StartDate 
  AND theDate <= isnull(EndDate, '31/12/2012')

SELECT 
 ROW_NUMBER() OVER (ORDER BY PersonID, theDate) AS rowid,
 PersonID, 
 theDate, 
 STUFF((
  SELECT '/' + Description
  FROM #SplitRanges AS s
  WHERE s.PersonID = sr.PersonID 
    AND s.theDate = sr.theDate
  FOR XML PATH('')
  ), 1, 1,'') AS Descriptions
INTO #MergedDescriptions
FROM #SplitRanges AS sr
GROUP BY PersonID, theDate


SELECT 
 ROW_NUMBER() OVER (ORDER BY PersonID, theDate) AS ID, 
 *
INTO #InterimResults
FROM
(
 SELECT * 
 FROM #MergedDescriptions AS t1
 WHERE NOT EXISTS 
  (SELECT 1 
   FROM #MergedDescriptions AS t2 
   WHERE t1.PersonID = t2.PersonID 
     AND t1.RowID - 1 = t2.RowID 
     AND t1.Descriptions = t2.Descriptions)
UNION ALL
 SELECT * 
 FROM #MergedDescriptions AS t1
 WHERE NOT EXISTS 
  (SELECT 1 
   FROM #MergedDescriptions AS t2 
   WHERE t1.PersonID = t2.PersonID 
     AND t1.RowID = t2.RowID - 1
     AND t1.Descriptions = t2.Descriptions)
) AS t

SELECT DISTINCT 
 PersonID, 
 Surname, 
 FirstName
INTO #DistinctPerson
FROM Schedule

SELECT 
 t1.PersonID, 
 dp.Surname, 
 dp.FirstName, 
 t1.Descriptions, 
 t1.theDate AS StartDate, 
 CASE 
  WHEN t2.theDate = '31/12/2012' THEN NULL 
  ELSE t2.theDate 
 END AS EndDate
FROM #DistinctPerson AS dp
JOIN #InterimResults AS t1 
 ON t1.PersonID = dp.PersonID
JOIN #InterimResults AS t2 
 ON t2.PersonID = t1.PersonID 
  AND t1.ID + 1 = t2.ID 
  AND t1.Descriptions = t2.Descriptions

DROP TABLE #SplitRanges
DROP TABLE #MergedDescriptions
DROP TABLE #DistinctPerson
DROP TABLE #InterimResults

/*

DROP TABLE Schedule

*/

以上解决方案还可以处理附加描述之间的间隙，因此，如果您为PersonID 18添加另一个描述并留下空隙：

INSERT INTO Schedule VALUES (18, 'Smith', 'John', 'Gym', '10/02/2009', '28/02/2009')

它将适当地填补这个空缺。正如评论中指出的那样，您不应该在这个表中有名称信息，应该将其规范化为一个人员表，可以在最终结果中JOIN到该表中。我通过使用SELECT DISTINCT构建一个临时表来模拟这个其他表以创建该JOIN。

- Jonathan Kehayias

Answer 2

试试这个

SET DATEFORMAT dmy
DECLARE @Membership TABLE( 
    PersonID    int, 
    Surname     nvarchar(16), 
    FirstName   nvarchar(16), 
    Description nvarchar(16), 
    StartDate   datetime, 
    EndDate     datetime)   
INSERT INTO @Membership VALUES (18, 'Smith', 'John', 'Poker Club', '01/01/2009', NULL)
INSERT INTO @Membership VALUES (18, 'Smith', 'John','Library', '05/01/2009', '18/01/2009')
INSERT INTO @Membership VALUES (18, 'Smith', 'John','Gym', '10/01/2009', '28/01/2009')
INSERT INTO @Membership VALUES (26, 'Adams', 'Jane','Pilates', '03/01/2009', '16/02/2009')

--Program Starts
declare @enddate datetime
--Measuring extreme condition when all the enddates are null(i.e. all the memberships for all members are in progress)
-- in such a case taking any arbitary date e.g. '31/12/2009' here else add 1 more day to the highest enddate
select @enddate =  case when max(enddate) is null then '31/12/2009' else max(enddate) + 1 end from @Membership

--Fill the null enddates
; with fillNullEndDates_cte as
(
    select
            row_number() over(partition by PersonId order by PersonId) RowNum
            ,PersonId
            ,Surname
            ,FirstName
            ,Description
            ,StartDate
            ,isnull(EndDate,@enddate) EndDate
    from @Membership
)
--Generate a date calender
, generateCalender_cte as
(
    select 
        1 as CalenderRows
        ,min(startdate) DateValue
    from @Membership
       union all
        select 
            CalenderRows+1
            ,DateValue + 1
        from    generateCalender_cte   
        where   DateValue + 1 <= @enddate
)
--Generate Missing Dates based on Membership
,datesBasedOnMemberships_cte as
 (
    select 
            t.RowNum
            ,t.PersonId
            ,t.Surname
            ,t.FirstName
            ,t.Description          
            , d.DateValue
            ,d.CalenderRows
    from generateCalender_cte d 
    join fillNullEndDates_cte t ON d.DateValue between t.startdate and t.enddate
)
--Generate Dscription Based On Membership Dates
, descriptionBasedOnMembershipDates_cte as
(
    select    
        PersonID
        ,Surname
        ,FirstName
        ,stuff((
            select '/' + Description
            from datesBasedOnMemberships_cte d1
            where d1.PersonID = d2.PersonID 
            and d1.DateValue = d2.DateValue
            for xml path('')
        ), 1, 1,'') as Description
        , DateValue
        ,CalenderRows
    from datesBasedOnMemberships_cte d2
    group by PersonID, Surname,FirstName,DateValue,CalenderRows
)
--Grouping based on membership dates
,groupByMembershipDates_cte as
(
    select d.*,
    CalenderRows - row_number() over(partition by Description order by PersonID, DateValue) AS  [Group]
    from descriptionBasedOnMembershipDates_cte d
)
select PersonId
,Surname
,FirstName
,Description
,convert(varchar(10), convert(datetime, min(DateValue)), 103) as StartDate
,case when max(DateValue)= @enddate then null else convert(varchar(10), convert(datetime, max(DateValue)), 103) end as EndDate
from groupByMembershipDates_cte 
group by [Group],PersonId,Surname,FirstName,Description
order by PersonId,StartDate
option(maxrecursion 0)

- priyanka.sarkar

Answer 3

[只是许多年后。]

我创建了一个存储过程，可以通过单个表中的分区对齐和分段，并且您可以使用这些对齐的断点来使用子查询和XML PATH将描述旋转到一个不规则列中。

看看下面是否有帮助：

1. 文档：https://github.com/Quebe/SQL-Algorithms/blob/master/Temporal/Date%20Segment%20Manipulation/DateSegments_AlignWithinTable.md

2. 存储过程：https://github.com/Quebe/SQL-Algorithms/blob/master/Temporal/Date%20Segment%20Manipulation/DateSegments_AlignWithinTable.sql

例如，您的调用可能如下所示：

EXEC dbo.DateSegments_AlignWithinTable
@tableName = 'tableName',
@keyFieldList = 'PersonID',
@nonKeyFieldList = 'Description',
@effectivveDateFieldName = 'StartDate',
@terminationDateFieldName = 'EndDate'

您需要将结果（即表格）捕获到另一个表或临时表中（假设在以下示例中称为“AlignedDataTable”）。然后，您可以使用子查询进行数据透视。

SELECT 
    PersonID, StartDate, EndDate,

    SUBSTRING ((SELECT ',' + [Description] FROM AlignedDataTable AS innerTable 
        WHERE 
            innerTable.PersonID = AlignedDataTable.PersonID
            AND (innerTable.StartDate = AlignedDataTable.StartDate) 
            AND (innerTable.EndDate = AlignedDataTable.EndDate)
        ORDER BY id
        FOR XML PATH ('')), 2, 999999999999999) AS IdList

 FROM AlignedDataTable
 GROUP BY PersonID, StartDate, EndDate 
 ORDER BY PersonID, StartDate

- Dennis Quebe