CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Scales
Scales

Reputation: 43

Update end date based on its succeeding start date in sql server

I am new to SQL Server, I tried few methods but couldn't able to get succeed to update below nulls with the value of their immediate successive to respective products (start_day-1 day), It is my production scenario, so I cant able to publish original query I tried. So kindly help me to achieve this scenario.

Table_Name - Product 

Actual data:
------------------------------------------
Product_cd |    Start_date |    end_date
------------------------------------------
A          |    2017-01-01 |    2017-01-10
A          |    2017-01-11 |    null
A          |    2017-03-10 |    2099-12-31
B          |    2015-01-01 |    null
B          |    2017-01-11 |    2099-12-31
C          |    2015-01-01 |    2015-01-10
C          |    2015-01-11 |    null
C          |    2015-03-10 |    2015-03-09
C          |    2015-03-10 |    2099-12-31
D          |    2000-01-01 |    2000-10-21
D          |    2000-10-22 |    2000-11-12
D          |    2000-11-13 |    null
D          |    2015-03-10 |    2099-12-31

Correct data expecting: (After Null in end_date, min(start_date) for same product- 1 day)

------------------------------------------
Product_cd |    Start_date |    end_date
------------------------------------------
A          |    2017-01-01 |    2017-01-10
A          |    2017-01-11 |    2017-03-09
A          |    2017-03-10 |    2099-12-31
B          |    2015-01-01 |    2017-01-10
B          |    2017-01-11 |    2099-12-31
C          |    2015-01-01 |    2015-01-10
C          |    2015-01-11 |    2015-03-09
C          |    2015-03-10 |    2015-03-09
C          |    2015-03-10 |    2099-12-31
D          |    2000-01-01 |    2000-10-21
D          |    2000-10-22 |    2000-11-12
D          |    2000-11-13 |    2015-03-09
D          |    2015-03-10 |    2099-12-31

Upvotes: 0

Views: 2316

Answers (4)

etsa
etsa

Reputation: 5060

To extract the values you want you can use following query. It use windows analytical function LEAD() to find next value for a PRODUCT_CD, using START_DATE ordering). (As Gordon pointed out, in MSSQL 2012+)

SELECT *
FROM (SELECT PRODUCT_CD, START_DATE, END_DATE
       , LEAD(START_DATE) OVER (PARTITION BY PRODUCT_CD ORDER BY START_DATE)-1  AS DATE_SUCC
      FROM PRODUCT) A 
WHERE END_DATE IS NULL AND DATE_SUCC IS NOT NULL;

Try to make the UPDATE by yourself. If you find any problem let me know and we'll see together.

I thought it would be useful for you to try to do the UPDATE, but others don't think so. Here is the UPDATE, starting from my SELECT (I don't think CTE is necessary). I used it inside a BEGIN TRAN / ROLLBACK TRAN, so you can check it.

BEGIN TRAN
UPDATE A SET END_DATE = A.DATE_SUCC
FROM (SELECT PRODUCT_CD, START_DATE, END_DATE
      , LEAD(START_DATE) OVER (PARTITION BY PRODUCT_CD ORDER BY START_DATE)-1  AS DATE_SUCC
      FROM PRODUCT) A 
WHERE A.END_DATE IS NULL AND A.DATE_SUCC IS NOT NULL
SELECT * FROM PRODUCT
ROLLBACK TRAN

Output sample:

PRODUCT_CD  START_DATE              END_DATE
A           2017-01-01 00:00:00.000 2017-01-10 00:00:00.000
A           2017-01-11 00:00:00.000 2017-03-09 00:00:00.000
A           2017-03-10 00:00:00.000 2099-12-31 00:00:00.000
B           2015-01-01 00:00:00.000 2017-01-10 00:00:00.000
B           2017-01-11 00:00:00.000 2099-12-31 00:00:00.000
...

Upvotes: 0

Guna
Guna

Reputation: 1

Try this.....

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) rownum,* INTO #Temp_table 
FROM dbo.StartEnd f1

SELECT t1.Product_cd,t1.Startdate,DATEADD(DAY,-1,t2.Startdate)end_date
FROM #Temp_table t1
LEFT JOIN #Temp_table t2 ON t1.rownum = t2.rownum - 1

Upvotes: 0

SQLBadPanda
SQLBadPanda

Reputation: 635

As etsa says the LEAD window function is what you need to use here (see here). You can only put this in a SELECT though so your update will need to be via something like a CTE. Try something like this...

DROP TABLE IF EXISTS StartEnd
CREATE TABLE StartEnd
(   Product_cd char(1),
    Startdate date,
    end_date date
)
INSERT dbo.StartEnd (Product_cd,Startdate,end_date)
VALUES 
('A','2017-01-01','2017-01-10' ),
('A','2017-01-11',null         ),
('A','2017-03-10','2099-12-31' ),
('B','2015-01-01',null         ),
('B','2017-01-11','2099-12-31' ),
('C','2015-01-01','2015-01-10' ),
('C','2015-01-11',null         ),
('C','2015-03-10','2015-03-09' ),
('C','2015-03-10','2099-12-31' ),
('D','2000-01-01','2000-10-21' ),
('D','2000-10-22','2000-11-12' ),
('D','2000-11-13',null         ),
('D','2015-03-10','2099-12-31' );

SELECT * FROM dbo.StartEnd AS se;

WITH UpdateRows AS
(
    SELECT se.Product_cd,
           se.Startdate,
           se.end_date,
           CASE WHEN se.end_date IS NULL 
                THEN dateadd(DAY,-1,lead(se.StartDate,1) OVER(PARTITION BY se.Product_cd ORDER BY se.Startdate))
                ELSE se.end_date END AS newEndDate
    FROM dbo.StartEnd AS se
)
UPDATE UpdateRows
SET end_date = newEndDate
WHERE end_date IS NULL;

SELECT * FROM dbo.StartEnd AS se;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269743

In SQL Server 2012+, you can use lead(). In earlier versions, you need another method. Here is one:

update p
    set end_date = dateadd(day, -1, p2.start_date)
    from product p outer apply
         (select top 1 p2.*
          from product p2
          where p2.product_cd = p.product_cd and
                p2.start_date > p.start_date
          order by p2.start_date desc
         ) p2
     where p.end_date is null;

If you just want to retrieve the data, then you can use the same from clause in a select.

Upvotes: 0

Related Questions