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