Update Start date using end date.

Question

Update Start date using end date.

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLian SSC-Addicted Points: 422 More actions · Answer 1

It would probably have been helpful to show the current select query. However, it feels like the LEAD function may help. An example of usage is:

SELECT *
, STATUS_CHANGE_DATE StartDate
, LEAD(status_change_date) over (order by status_change_date) EndDate
from temp_date

I would also question why you are storing dates as integers.

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 2

Thanks sir for your help but is not working properly to get output what I would expect.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

This is the solution I posted in the other thread:

-- SQL 2012 and later
; WITH prevs AS (
   SELECT *, LAG(end_date) OVER (ORDER BY START_DATE) AS prev_end_date
   FROM   temp_date
)
UPDATE prevs
SET    STATUS_CHANGE_DATE = convert(char(8), prev_end_date)
WHERE  STATUS = 'active'
  AND  prev_end_date IS NOT NULL   
go
-- SQL 2008 and SQL 2005
; WITH numbering AS (
   SELECT *, row_number() OVER( ORDER BY START_DATE) AS rowno
   FROM   temp_date
)
UPDATE a
SET    STATUS_CHANGE_DATE = convert(char(8), b.END_DATE)
FROM   numbering a
JOIN   numbering b ON b.rowno = a.rowno - 1

To which Kiran replied that it was not working properly, but did not explain in which manner, so I in my turned replied and asked for clarification.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

drew.allen SSC Guru Points: 77014 More actions · Answer 4

First, simply stating that something "is not working properly" is not very informative. Are you getting an error message? Are getting wrong results? How is it not working?

Second, your sample data matches your expected outcomes. How do you expect us to help you get from starting data to your expected outcome if we only have your expected outcome?

Finally, you should not be using code that you do not understand, because you are the one who will be supporting it. The solution is going to use either LEAD() or LAG(). If you cannot figure out how to modify the code to work with your data, you probably should not be using the code.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA