根据SQL Server中的成功开始日期更新结束日期

Question

我是SQL Server的新手，我尝试了几种方法，但无法成功地以其直接连续连续到相应产品的价值（start_day-day-1 Day）更新下方。，这是我的生产场景，因此我无法发布我尝试过的原始查询。因此，请帮助我实现这种情况。

table_name-产品

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

正确的数据期望：（ null in end_date，nim，min（start_date）持有同一产品-1天）

------------------------------------------
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

Answer 1

正如ETSA所说，引导窗口函数是您需要在此处使用的（请参阅此处）。您只能将其放入选择中，因此您的更新需要通过CTE之类的东西进行。尝试这样的东西...

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;

Answer 2

在SQL Server 2012 中，您可以使用lead()。在早期版本中，您需要另一种方法。这是一个：

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;

如果您只想检索数据，则可以在select中使用相同的from子句。

Answer 3

尝试这个.....

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

Answer 4

提取要使用以下查询的值。它使用Windows Analytical函数Lead（）使用start_date Ordering找到product_cd的下一个值）。（正如戈登指出的那样，在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;

尝试自己进行更新。如果您发现任何问题，请告诉我，我们会一起看到。

我认为尝试进行更新对您来说是有用的，但其他人则不认为这是有用的。这是从我的选择开始的更新（我认为CTE不需要）。我在开始的tran/urlback tran中使用了它，因此您可以检查它。

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

输出样本：

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
...