從僅包含開始日期和分區的列表中計算開始日期和結束日期

我目前正在使用具有以下表結構的數據集

create or replace TABLE FACT_TBL_PRODUCTSUPPLIER (
   DATEID NUMBER(18,0) NOT NULL,
   PRODUCTNUMBER NUMBER(38,0) NOT NULL,
   SUPPLIERNUMBER NUMBER(38,0) NOT NULL
);

範例數據集：

   DATEID        PRODUCTNUMBER SUPPLIERNUMBER
   2019-07-06    555           1234
   2019-07-06    343           545
   2019-07-10    555           8888
   2019-07-16    555           1234
   2019-08-01    555           1234
   2019-09-15    555           6666

我試圖將產品編號與供應商編號保持聯繫，直到產品被另一家供應商接管。當產品確實被接管時，我應該再為產品添加 30 天，以便它保持連結。

預期輸出：

   DATEID       END_DATEID    PRODUCTNUMBER SUPPLIERNUMBER
   2019-07-06   2019-08-07              555           1234
   2019-07-06   2020-02-06              343           545
   2019-07-10   2019-08-13              555           8888
   2019-07-16   2019-08-29              555           1234
   2019-08-01   2019-10-13              555           1234
   2019-09-15   2020-02-06              555           6666

到目前為止，我已經嘗試了以下選項：

select td.dateid, 
LEAD(DATEADD(day, +28, dateid)) OVER(ORDER BY dateid) AS EndDate,
productnumber, ps.suppliernumber,
from FACT_TBL_PRODUCTSUPPLIER PS 

---

select
   distinct dateid,
   coalesce(
       dateid,
       lag(dateid, 1) over(
           partition by
               productnumber,
               suppliernumber
           order by
               dateid asc
       )
   ) as dateid,
   dateadd(
       'day',
       28,
       coalesce(
           lead(dateid) over(
               partition by
                   productnumber,
                   suppliernumber
               order by
                   dateid asc
           ),
           current_date()
       )
   ) end_dateid,
   productnumber,
   suppliernumber
from
   FACT_TBL_PRODUCTSUPPLIER; 

如果沒有人接管產品編號，則已分配產品的目前供應商應該能夠看到它。

我想做的是在有人接管之前應該向供應商展示產品。當有人接管時，結束日期將額外顯示 28 天。如果沒有人接管特定產品，則供應商應該能夠無限期地看到它，例如目前日期。

為了確保我的回答正確，我將重申要求。結果集應顯示供應商控制產品的第一個日期 (DATEID) 和另一個供應商控制產品時產品可見的最後日期 (ENDDATEID)。

ENDDATEID 應在下一個供應商控制產品的日期之前增加 28 天。如果沒有新的供應商控制，ENDDATEID 應顯示目前日期 + 28 天。

如果我正確地說明了這一點，您可以使用 LEAD 和 CTE 來返回您正在尋找的結果集。下面的程式碼和這裡的工作範例：

WITH CTE AS (
SELECT DATEID
 ,LEAD(DATEID, 1, GETDATE()) OVER (PARTITION BY PRODUCTNUMBER ORDER BY DATEID) AS TAKEOVERDATE
 ,PRODUCTNUMBER
 ,SUPPLIERNUMBER
FROM FACT_TBL_PRODUCTSUPPLIER
)

SELECT DATEID
 ,DATEADD(DAY, (DATEDIFF(DAY, DATEID, TAKEOVERDATE))+28, DATEID) AS ENDDATEID
 ,PRODUCTNUMBER
 ,SUPPLIERNUMBER
FROM CTE

輸出：

DATEID      ENDDATEID   PRODUCTNUMBER   SUPPLIERNUMBER
----------------------------------------------------
2019-07-06  2020-02-27  343             545
2019-07-06  2019-08-07  555             1234
2019-07-10  2019-08-13  555             8888
2019-07-16  2019-08-29  555             1234
2019-08-01  2019-10-13  555             1234
2019-09-15  2020-02-27  555             6666

在 LEAD 語法中，我們按 PRODUCTNUMBER 對數據進行分區，以確保我們只獲得與該行所代表的產品相關的日期。然後我們按順序獲取下一個 DATEID（在 LEAD 語法中定義為 DATEID 升序）。如果不存在下一個日期值，我們將獲得目前日期。這為我們提供了產品被接管的日期（如果尚未被接管，則為目前日期）。

從這裡開始，我們可以在 CTE 之外使用 DATEADD 將 28 天添加到“TAKEOVERDATE”列，以生成所需的 ENDDATEID 值。

注意：此語法適用於 SQL Server。您沒有說明 RDBMS，但標記了問題 T-SQL，所以我假設 SQL Server。

引用自：https://dba.stackexchange.com/questions/258424