CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Ben
Ben

Reputation: 31

SQL multiple start dates to end date

I have a table with the following format (which I cannot change)

ClientID  |  RefAd1  |  Cluster Start Date  |  Cluster End Date

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

What I would like to come out with is:

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

I've searched on here, and had many attempts myself, but just can't get it working.

I can't update the source table (or add another table into the database) so I'm going to do this in a view (which I can save)

Any help would be gratefully appreciated, been going round in circles with this for a day and a bit now!

Upvotes: 3

Views: 532

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Here is the script if you just want the view you described:

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

Upvotes: 1

Sarath Subramanian
Sarath Subramanian

Reputation: 21271

Use Self join to get next record

;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

EDIT :

To update the table, you can use the below query

;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]

EDIT 2 :

If you want this to be done for ClientId and 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

If you want to do it only for ClientId, remove the conditions for RefAd1

Upvotes: 1

Related Questions