Update Start date using end date.

Question

Post reply

Update Start date using end date.

kiran.rajenimbalkar

SSCrazy

Points: 2603

November 26, 2019 at 12:51 pm

#3701041

Hi Sir,

I want to set the END_DATE columns value whose status is "Suspended", into the column STATUS_CHANGE_DATE whose status is "Active", except first row.Please suggest me how to update these values?.

--create table script--

CREATE TABLE temp_date
(ID INT,
STATUS NVARCHAR(10),
STATUS_CHANGE_DATE DATE,
START_DATE INT,
END_DATE INT,
ROW_NUM INT);
     
--Insert Into script---

INSERT INTO temp_date VALUES (123,'Active','1991-01-09',19910109,NULL,1);
INSERT INTO temp_date VALUES (234,'Suspended','1997-03-13',19970313,19970507,2);
INSERT INTO temp_date VALUES (456,'Active','1997-05-07',NULL,NULL,3);
INSERT INTO temp_date VALUES (678,'Suspended','2000-01-01',20000101,20000328,4);
INSERT INTO temp_date VALUES (890,'Active','2000-03-28',NULL,NULL,5);
My Current out put is as

My Current out put is as

IDSTATUS     STATUS_CHANGE_DATESTART_DATEEND_DATEROW_NUM
123Active1991-01-0919910109NULL1
234Suspended1997-03-1319970313199705072
456Active1991-01-09NULLNULL3
678Suspended2000-01-0120000101200003284
890Active1991-01-09NULLNULL5

Expected Output should be

IDSTATUSSTATUS_CHANGE_DATESTART_DATEEND_DATEROW_NUM
123Active1991-01-0919910109NULL1
234Suspended1997-03-1319970313199705072
456Active1997-05-07NULLNULL3
678Suspended2000-01-0120000101200003284
890Active2000-03-28NULLNULL5

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

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

SQLian SSC-Addicted Points: 413 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: 76985 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