Want to update enddate column data from the previous startdate data

Question

Post reply

Want to update enddate column data from the previous startdate data

kiran.rajenimbalkar

SSCrazy

Points: 2603

December 22, 2021 at 10:03 am

#3966247

Hi Sir,

I have some data, and I want to update the enddate columns data from table abc which has previous startdate for those having ind=0, but the Ind=1 has current record in the table and it should be as it is,.

I have populated sample data into the abc2_sample, and I want to update the enddate column same like this.

CREATE TABLE abc
(id INT,
subid INT,
ind bit,
startdate datetime2,
enddate datetime2
)

go

INSERT INTO abc VALUES (111,3000,0,'2021-09-20 00:00:00.000','2021-10-12 23:59:59.999')
INSERT INTO abc VALUES (111,3000,0,'2021-10-13 00:00:00.000','2079-06-06 23:59:59.999')
INSERT INTO abc VALUES (111,3000,1,'2021-10-20 00:00:00.000','2079-06-06 23:59:59.999')

go


CREATE TABLE abc2_sample
(id INT,
subid INT,
ind bit,
startdate datetime2,
enddate datetime2
)

go

INSERT INTO abc2_sample VALUES (111,3000,0,'2021-09-20 00:00:00.000','2021-10-12 23:59:59.999')
INSERT INTO abc2_sample VALUES (111,3000,0,'2021-10-13 00:00:00.000','2021-10-19 23:59:59.999')
INSERT INTO abc2_sample VALUES (111,3000,1,'2021-10-20 00:00:00.000','2079-06-06 23:59:59.999')

go

SELECT * FROM abc
SELECT * FROM abc2_sample

Please suggest me how to achieve this.

Regards

KiranR

Viewing 3 posts - 1 through 2 (of 2 total)

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

Phil Parkin SSC Guru Points: 246956 More actions · Answer 1

Something like this? It makes a few assumptions about how your data is ordered. What is the PK of this table?

WITH ordered
AS (SELECT a.enddate
          ,rn = ROW_NUMBER() OVER (PARTITION BY a.subid ORDER BY a.startdate DESC)
          ,ed = DATEADD(
                           MILLISECOND
                          ,-1
                          ,LEAD(a.startdate, 1, '99991231') OVER (PARTITION BY a.subid ORDER BY a.startdate)
                       )
    FROM dbo.abc a
    WHERE EXISTS
    (
        SELECT 1 FROM dbo.abc a2 WHERE a2.subid = a.subid AND a2.ind = 1
    ))
UPDATE ordered
SET ordered.enddate = ordered.ed
WHERE ordered.rn = 2;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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

Hi Sir,

There is no any PK , I just want to update the end date on the basis of id,subid and ind column, and as per above solution, its worked.

Thank you so much Sir for your help.