SQL多个开始日期到结束日期

Question

我有一个具有以下格式的表(我不能更改)

ClientID  |  RefAd1  |  Cluster Start Date  |  Cluster End Date

100001    |   R1234  |    2014-11-01        |     
100001    |   R1234  |    2014-11-10        |  
100001    |   R1234  |    2014-11-20        |

我想说的是：

ClientID  |  RefAd1  |  Cluster Start Date  |  Cluster End Date

100001    |   R1234  |    2014-11-01        |     2014-11-10          
100001    |   R1234  |    2014-11-10        |     2014-11-20    
100001    |   R1234  |    2014-11-20        |     NULL

我在这里搜索过，我自己也做过很多尝试，但都没能成功。

我无法更新源表(或者将另一个表添加到数据库中)，所以我将在视图(可以保存)中这样做。

任何帮助都将是感激的感谢，与此进行了一天和一点时间的循环！

Answer 1

使用自联接获取下一条记录

;WITH CTE AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY [Cluster Start Date])RNO,*
    FROM YOURTABLE
)
SELECT C1.ClientID,C1.RefAd1,C1.[Cluster Start Date],C2.[Cluster Start Date] [Cluster End Date]
FROM CTE C1
LEFT JOIN CTE C2 ON C1.RNO=C2.RNO-1 

• 单击此处查看结果

编辑:

要更新表，可以使用以下查询

;WITH CTE AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY [Cluster Start Date])RNO,*
    FROM #TEMP
)
UPDATE #TEMP SET [Cluster End Date] = TAB.[Cluster End Date]
FROM
(
    SELECT C1.ClientID,C1.RefAd1,C1.[Cluster Start Date],C2.[Cluster Start Date] [Cluster End Date]
    FROM CTE C1
    LEFT JOIN CTE C2 ON C1.RNO=C2.RNO-1 
)TAB
WHERE TAB.[Cluster Start Date]=#TEMP.[Cluster Start Date]

• 单击此处查看结果

编辑2 :

如果您希望为ClientId和RefAd1这样做。

;WITH CTE AS
(
    -- Get current date and next date for each type of ClientId and RefAd1
    SELECT ROW_NUMBER() OVER(PARTITION BY ClientID,RefAd1 ORDER BY [Cluster Start Date])RNO,*
    FROM #TEMP
)
UPDATE #TEMP SET [Cluster End Date] = TAB.[Cluster End Date]
FROM
(
    SELECT C1.ClientID,C1.RefAd1,C1.[Cluster Start Date],C2.[Cluster Start Date] [Cluster End Date]
    FROM CTE C1
    LEFT JOIN CTE C2 ON C1.RNO=C2.RNO-1 AND C1.ClientID=C2.ClientID AND C1.RefAd1=C2.RefAd1
)TAB
WHERE TAB.[Cluster Start Date]=#TEMP.[Cluster Start Date] AND TAB.ClientID=#TEMP.ClientID AND TAB.RefAd1=#TEMP.RefAd1

• 单击此处查看结果

如果只想为ClientId执行此操作，请删除RefAd1的条件。

Answer 2

如果您只想要您描述的视图，下面是脚本：

CREATE VIEW v_name as
SELECT 
  ClientId, 
  RefAd1, 
  [Cluster Start Date], 
  ( SELECT 
      min([Cluster Start Date])
    FROM yourTable 
    WHERE 
      t.[Cluster Start Date] < [Cluster Start Date]
) as [Cluster End Date]
FROM yourtable t