T-SQL, Snowflake – Calculate Start and End Dates from Start Dates List

snowflaket-sql

I'm currently working with a data set with the following table structure

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

Example Data Set:

    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

I'm trying to keep the product number linked to the supplier number until the product gets taken over by another supplier. When the product does get taken over then I should add another 30 days to the product so it remains linked.

Expected Output:

    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

I have tried the following options so far:

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;

If no one takes over the product number then the current supplier that has been assigned the product should be able to see it.

What I'm trying to do is a product should be displayed to a supplier until someone takes over. When someone does takeover the end date would have an additional 28 days to display. If no one takes over a specific product then the supplier should be able to see it indefinitely e.g. current date.

Best Answer

To make sure I've answered correctly, I'll restate the requirement. The result set should show the first date (DATEID) that a SUPPLIER took control of a PRODUCT and the last date the PRODUCT was visible (ENDDATEID) when another SUPPLIER took control.

ENDDATEID should add an additional 28 days to the date the next SUPPLIER took control of a PRODUCT. If no new SUPPLIER has taken control, the ENDDATEID should show the current date + 28 days.

If I've stated this correctly, you can use LEAD and a CTE to return the result set you're looking for. Code below, and working example here:

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

Output:

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

In the LEAD syntax, we're partitioning the data by PRODUCTNUMBER to ensure we only get dates relevant to the product that row represents. We then get the next DATEID in order (defined as DATEID ascending in the LEAD syntax). If no next date value exists, we get the current date. This gives us the date a product was taken over (or the current date if it hasn't been taken over).

From here, we can use DATEADD outside the CTE to add 28 days to the 'TAKEOVERDATE' column to produce the desired ENDDATEID value.

NOTE: This syntax is for SQL Server. You haven't stated an RDBMS, but tagged the question T-SQL so I'm assuming SQL Server.

Related Question