Concat values from two time-stamps within over-partition function

Question

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

INSERT INTO operations
(time_stamp, product, plan_week, quantity
)
VALUES 
("2020-01-01", "Product_A", "CW01", "125"),
("2020-01-01", "Product_B", "CW01", "300"),
("2020-01-01", "Product_C", "CW01", "700"),
("2020-01-01", "Product_D", "CW01", "900"),
("2020-01-01", "Product_G", "CW01", "600"),

("2020-03-15", "Product_A", "CW01", "570"),
("2020-03-15", "Product_C", "CW02", "150"),
("2020-03-15", "Product_E", "CW02", "325"),
("2020-03-15", "Product_G", "CW05", "482");

Expected Results:

time_stamp     product    plan_week     quantity    week_switched    plan_week     plan_week_switch
2020-01-01    Product_A     CW01         125            no             CW01            no
2020-03-15    Product_A     CW01         570            no             CW01            no
2020-01-01    Product_B     CW01         300            no             CW01            no
2020-01-01    Product_C     CW01         700            yes            CW01         CW01-to-CW02
2020-03-15    Product_C     CW02         150            yes            CW02         CW01-to-CW02
2020-01-01    Product_D     CW01         900            no             CW01            no
2020-03-15    Product_E     CW02         325            no             CW02            no 
2020-01-01    Product_G     CW01         600            yes            CW01         CW01-to-CW05
2020-03-15    Product_G     CW05         482            yes            CW05         CW01-to-CW05

In the above result I check if the plan_week of a product has switched between two time_stamps.
For this I use the following query:

SELECT 
time_stamp,
product,
plan_week,
quantity,
 (CASE WHEN MIN(plan_week) over (partition by product) = MAX(plan_week) over (partition by product)
 THEN 'no' else 'yes' END) as week_switched,
plan_week
FROM operations
GROUP BY 1,2
ORDER BY 2,1;

All this works perfectly.

Now, I want to add a column called plan_week_switch to the results.
In this column I want to describe how the weeks have switched.
Basically, something like this:

CONCAT(plan_week in first time_stamp, "-to-" , plan_week in second time_stamp)

How do I need to modify the query to get this column in the expected result?

score 0 · Accepted Answer · answered Sep 21 '20 at 13:50

I beieve you already had the most of the answer in your question.

SELECT time_stamp
       , product
       , plan_week
       , quantity
       , (CASE WHEN MIN(plan_week) over (partition by product) = MAX(plan_week) over (partition by product) THEN 
                   'no' 
               ELSE 'yes' 
          END) as week_switched
       ,(CASE WHEN MIN(plan_week) over (partition by product) = MAX(plan_week) over (partition by product) THEN 
                   'no' 
               ELSE 
                   concat(cast(MIN(plan_week) over (partition by product) as char), '-to-', MAX(plan_week) over (partition by product)) 
         END) as plan_week_switch
       , plan_week
FROM operations
GROUP BY 1,2
ORDER BY 2,1;

Here is the demo:

DEMO

Concat values from two time-stamps within over-partition function

1 Answers1

Linked