在开始/结束日期的多个记录中分割记录 [英] split record in multiple records on start/end date

问题描述

我正在寻找一个解决方案，我必须使用另一个表的数据从一个记录创建一组记录。表定义：

I'm looking for a solution where I have to create a set of records from one record using data from another table. The table definition:

DECLARE A AS TABLE
(
 AID BIGINT NOT NULL,
 StartDate DATETIME NOT NULL,
 EndDate DATETIME
)

DECLARE B AS TABLE
(
 AID BIGINT NOT NULL,
 StartDate DATETIME NOT NULL,
 EndDate DATETIME NULL
)

想法是当A包含：

1 | 01-01-2010 | 01-02-2010
2 | 01-10-2010 | 31-10-2010

和B包含：

1 | 01-01-2010 | 15-01-2010
2 | 15-10-2010 | 20-10-2010

我们收到5条记录：

1 | 01-01-2010 | 15-01-2010
1 | 16-01-2010 | 01-02-2010
2 | 01-10-2010 | 15-10-2010
2 | 16-10-2010 | 20-10-2010
2 | 21-10-2010 | 31-10-2010

目前我们用A上的游标和B上的内循环游标我们必须在SQLServer（TSQL或最坏情况CLR）中执行此操作。

Currently we do this with a cursor on A and an inner loop cursor on B, we have to do this in SQLServer (TSQL or in worst case CLR)

有关如何将此作为选择的任何想法，以便光标的开销消失？

Any ideas on how to write this as a select so that the overhead of the cursor disappears?

推荐答案

DECLARE @A TABLE (AID BIGINT NOT NULL, StartDate DATETIME NOT NULL, EndDate DATETIME)
DECLARE @B TABLE (AID BIGINT NOT NULL, StartDate DATETIME NOT NULL, EndDate DATETIME NULL)

SET DATEFORMAT dmy
INSERT @A VALUES (1 ,'01-01-2010','01-02-2010')
INSERT @A VALUES (2 ,'01-10-2010','31-10-2010')
INSERT @B VALUES (1 ,'01-01-2010','15-01-2010')
INSERT @B VALUES (2 ,'15-10-2010','20-10-2010')

;WITH transitions as
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY AID ORDER BY startdate) Sequence
    FROM (
        SELECT A.AID, A.startdate
        FROM @a A
        UNION
        SELECT A.AID, B.startdate + 1
        FROM @A A
        INNER JOIN @b B ON B.startdate > A.startdate AND B.startdate < A.enddate
        UNION
        SELECT A.AID, B.enddate + 1
        FROM @A A
        INNER JOIN @b B ON B.enddate > A.startdate AND B.enddate < A.enddate
        UNION
        SELECT A.AID, A.enddate + 1
        FROM @a A
        WHERE A.enddate > A.startdate
    ) T
)   
SELECT T1.AID, T1.startdate startdate, T2.startdate - 1 enddate
FROM transitions T1
INNER JOIN transitions T2 ON T2.AID = T1.AID AND T2.Sequence = T1.Sequence + 1
ORDER BY T1.AID, T1.Sequence

这篇关于在开始/结束日期的多个记录中分割记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！