0

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.

  • Your algorythm explainations seems do not match the data shown. Does the source table which contains shown 6 records ONLY (no another records) must produce shown result, shown 6 records strongly? – Akina Jan 29 at 13:53
  • @Akina - Sorry but I don't understand what you mean when you say it doesn't match to the algorithm? I have narrowed it down to 6 rows but used 2 different product numbers and suppliers. The original table has close to a million rows of data. – VGJ Jan 29 at 14:03
  • I don't understand what you mean when you say it doesn't match to the algorithm? Imagine that you create empty table using your DDL and insert into it 6 shown records. Imagine that you have a query which solves your task, and it is executed over this table with this 6 records. Does its result will be equal to shown expected output? – Akina Jan 29 at 16:12
  • @Akina - Yes that's correct the expected output would be what I've added in the expected output. 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. – VGJ Jan 29 at 16:47
  • Well. Line 1 - you add 32 days to '2019-07-06' and obtain '2019-08-07'. Why? you tell about 28 days... Line 2 - you add 215 (!) days. Why? and so on - I do NOT see the corralation between your words and shown result yet. – Akina Jan 29 at 16:56
1

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.

| improve this answer | |
  • Thanks for your help on this. It worked flawlessly. – VGJ Jan 30 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.