CODEWITHSUNDEEP
sql
Melz
Melz

Reputation: 15

Update Sql query to set end date based on start date

enter image description here

:-Need help in sql query for updating a table

update query- set END_DATE of first record should be the start date of second record, for a particular id .-as highlighted in yellow(order by start date ).

Also the final record for each id ( in this case for id=1&2 , record with end date 12/31/9999 should not touch and leave as it is. How to do that?

Upvotes: 0

Views: 762

Answers (2)

Kevin Swann
Kevin Swann

Reputation: 1038

With SQL Server, another way is to use common table expressions (CTE):

; WITH myTableExt AS (
SELECT *, ROW_NUMBER() OVER( PARTITION BY id ORDER BY id, startDate ) AS rowNo
FROM myTable
WHERE 1 = 1
)

UPDATE a
SET a.endDate = DATEADD( day, -1, b.startDate )
FROM myTableExt a
INNER JOIN myTableExt b ON a.id = b.id
AND a.rowNo = b.rowNo - 1
WHERE 1 = 1

The first part uses a CTE to generate the original table but extended with the row-No 1..N for the rows in the date sequence grouped-by id & ordered-by date.

To clarify, this shows the generated CTE:

; WITH myTableExt AS (
SELECT *, ROW_NUMBER() OVER( PARTITION BY id ORDER BY id, startDate ) AS rowNo
FROM myTable
WHERE 1 = 1
)
SELECT * FROM myTableExt

The second part uses the generated CTE to update the actual table with a self-join on itself to set the end-date for row(N) to one day before the start-date of row(N+1).

The rows with the 'last' start dates are not updated as there will not be a match on the next row in the CTE.

Upvotes: 0

Luke Kubat
Luke Kubat

Reputation: 381

If you have SQL Server 2016+ you can use the LEAD function.

UPDATE  MyTable
SET     [END_DATE] = COALESCE([DesiredData].END_DATE, '9999-12-31')
FROM        MyTable
INNER JOIN  (   SELECT  
                    [id], 
                    [START_DATE],
                    LEAD([START_DATE]) OVER(PARTITION BY [id], ORDER BY [START_DATE]) [END_DATE]
                    
                FROM    MyTable
            ) AS [DesiredData]
ON          MyTable.[id] = [DesiredData].[id]
AND         MyTable.[START_DATE] = [DesiredData].[START_DATE]

If you do not, you could do a correlated subquery:

UPDATE  MyTable
SET     [END_DATE] =    COALESCE(
                            (   SELECT TOP 1 END_DATE
                                FROM        MyTable [NextRecord]
                                WHERE       [NextRecord].[id] = MyTable.id
                                AND         [NextRecord].[START_DATE] > MyTable.[START_DATE]
                                ORDER BY    NextRecord.[START_DATE]
                            ), 
                            '9999-12-31'
                        )

Upvotes: 2

Related Questions