Update data based on previous end date

Question

Update data based on previous end date

Raj0125 511

Hi,

I am looking to get the date update dates as below.

Update Effective StartDate + 4 seconds with previous EffectiveEnd_Date.

Sample data below.

User's image

Please suggest how to write update statment based on EffectiveEndDate.

SSingh-MSFT 16,371 Reputation points Moderator

2023-02-17T06:09:48.8833333+00:00

Hi @Raj0125,

Glad to know that the below response was helpful. Thank you for marking this answer as accepted. This will help others find useful answers faster.

Please feel free to take a survey on the relevant answer. You can help us improve by leaving feedback verbatim.

Have a good day!

Regards,

Shakti

Accepted answer

1 additional answer

Your answer

SSingh-MSFT 16,371 Reputation points Moderator

2023-02-17T06:09:48.8833333+00:00

Hi @Raj0125,

Glad to know that the below response was helpful. Thank you for marking this answer as accepted. This will help others find useful answers faster.

Please feel free to take a survey on the relevant answer. You can help us improve by leaving feedback verbatim.

Have a good day!

Regards,

Shakti

Answer 1

Hi @Raj0125

Only issue with Date sould not consider previous EffectiveEndDate when Rep_Id change.

Adding PARTITION BY in the OVER clause of LAG() function would solve this issue.

The code is as below:

CREATE TABLE #Temp (Skey INT,Rep_Id int,Effective_Start_Date DATETIME,Effective_End_Date DATETIME)
INSERT INTO #Temp VALUES
(1,11,'2021-09-03 02:31:52','2021-09-05 02:30:54'),
(2,11,'2021-09-05 02:30:59','2021-09-07 02:31:38'),
(3,11,'2021-09-07 02:31:43','2021-09-08 02:51:14'),
(4,11,'2021-09-08 02:51:19','2021-09-14 02:38:53'),
(5,12,'2021-09-03 02:31:52','2021-09-05 02:30:54'),
(6,12,'2021-09-05 02:30:59','2021-09-07 02:31:38'),
(7,12,'2021-09-07 02:31:43','2021-09-08 02:51:14'),
(8,12,'2021-09-08 02:51:19','2021-09-14 02:38:53')

;WITH CTE AS
(
 SELECT Skey,Effective_Start_Date,Effective_End_Date,
        DATEADD(SECOND,4,LAG(Effective_End_Date)OVER(PARTITION BY Rep_Id ORDER BY Skey)) AS New_Effective_Start_Date
 FROM #Temp
)
UPDATE CTE
SET Effective_Start_Date = New_Effective_Start_Date

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

LiHongMSFT-4306 31,571

Hi @Raj0125

Try this:

CREATE TABLE #Temp (Skey INT,Effective_Start_Date DATETIME,Effective_End_Date DATETIME)
INSERT INTO #Temp VALUES
(1,'2021-09-03 02:31:52','2021-09-05 02:30:54'),
(2,'2021-09-05 02:30:59','2021-09-07 02:31:38'),
(3,'2021-09-07 02:31:43','2021-09-08 02:51:14'),
(4,'2021-09-08 02:51:19','2021-09-14 02:38:53')

;WITH CTE AS
(
 SELECT Skey,Effective_Start_Date,Effective_End_Date,
        DATEADD(SECOND,4,LAG(Effective_End_Date)OVER(ORDER BY Skey)) AS New_Effective_Start_Date
 FROM #Temp
)
UPDATE CTE
SET Effective_Start_Date = New_Effective_Start_Date

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

LiHongMSFT-4306 31,571 Reputation points

2023-02-14T07:43:13.24+00:00
This is the output after UPDATE:

The first Effective_Start_Date value is NULL because there is no previous Effective_End_Date.

Of course, you could give it a default value by modifying code like this:

DATEADD(SECOND,4,LAG(Effective_End_Date,1,'2021-09-05 00:00:00')OVER(ORDER BY Skey)) AS New_Effective_Start_Date
Raj0125 511 Reputation points

2023-02-14T08:37:54.3366667+00:00

This is working as expected . But Initial start date is coming as Null

Pls suggest.
LiHongMSFT-4306 31,571 Reputation points

2023-02-14T08:44:29.1766667+00:00

So, for the initial start_date, what value you want to display?

As I comment above, you could give it a default value while it is NULL.
Raj0125 511 Reputation points

2023-02-14T11:03:07.89+00:00

I have one more column RepId if we see below data I have new Repoid is starting 12 Effective stratdate is taking previous End Date.But it should not EffectiveEndDate.

Starting Null date i am using update statment to updated same as InsertDate because Insert and EffectiveDate are same.

Only issue with Date sould not consider previous EffectiveEndDate when Rep_Id change.

Please suggest.

Share via

Update data based on previous end date

1 additional answer

Your answer