Compare values of timestamps and assign a value to each of them in case they have changed

DB-Fiddle

CREATE TABLE operations (
    id int auto_increment primary key,
    time_stamp DATE,
    product VARCHAR(255),
    plan_week VARCHAR(255)
);

INSERT INTO operations (time_stamp, product, plan_week)
VALUES 
("2020-01-01", "Product_A", "CW01"),
("2020-01-01", "Product_B", "CW01"),
("2020-01-01", "Product_C", "CW01"),

("2020-03-15", "Product_A", "CW01"),
("2020-03-15", "Product_B", "CW02"),
("2020-03-15", "Product_C", "CW02"),
("2020-03-15", "Product_D", "CW01");

Expected Result:

    time_stamp   |   product     |   plan_week   |   week_switch
  ---------------|---------------|---------------|----------------- 
    2020-01-01   |   Product_A   |     CW01      |       no
    2020-01-01   |   Product_B   |     CW01      |       yes
    2020-01-01   |   Product_C   |     CW01      |       yes
                 |               |               |
    2020-03-15   |   Product_A   |     CW01      |       no
    2020-03-15   |   Product_B   |     CW02      |       yes
    2020-03-15   |   Product_C   |     CW02      |       yes
    2020-03-15   |   Product_D   |     CW01      |       no

In the above result I want to list all products from the table and compare the two time_stamps to each other.
If the plan_week of one product has switched between the both time_stamps I want that in the additional column called week_switch the word yes gets inserted and if not the word no gets inserted.

I tried to go with this query but could not make it work:

SELECT time_stamp, product, plan_week,
        (CASE WHEN MIN(plan_week) <> MAX(plan_week) THEN 'yes' ELSE 'no' END) AS week_switch
FROM operations
GROUP BY 1,2;

What do I need to change to get the expected result?

Answer

I would just use window functions:

select o.*,
       (case when min(plan_week) over (partition by product) = max(plan_week) over (partition by product)
             then 'no' else 'yes'
        end) as switched
from operations o;

Advertisement

Answer