Stuck creating a 0/1 flag that works correctly. Finally giving in

user_14666164 0
Viewed 35

Evening all.

I am trying to create a very basic case statement which buckets three scenarios with a 0 or a 1 in mySQL. I have three fields found in the reference table link below.

Essentially I am trying to bucket any ProductID that has a StatusCD of 'I' and 'O' as 1, only 'I' as 1 and then anything with only an 'O' result and no corresponding 'I' as a 0. What this data is showing is a product coming into the warehouse ('I') and then exiting the warehouse ('O'). I have other fields which are capturing date differences but ultimately I am trying to create a flag to ignore scenarios where we only have Product#'s with an 'O' statusCD which would indicate their arrival to the warehouse was not logged appropriately and would skew our "Age in warehouse" buckets.

Any insight is greatly appreciated!

Reference Table with fields:

Date       | ProductID | StatusCD
2021-01-01 | U1000     | I
2021-01-10 | U1000     | O
2021-01-10 | U2000     | I
2021-01-15 | U3000     | O
case mysql
1 Answers

Assuming you want to retain every original record, you could use analytic functions here:

SELECT Date, ProductID, StatusCD,
       CASE WHEN SUM(StatusCD <> 'O') OVER (PARTITION BY ProductID) = 0
            THEN 0 ELSE 1 END As Label
FROM yourTable
ORDER BY Date;

screen capture from demo link below

Demo

For versions of MySQL earlier than 8+:

SELECT t1.Date, t1.ProductID, t1.StatusCD,
       CASE WHEN t2.OpenCount = 0 THEN 0 ELSE 1 END AS Label
FROM yourTable t1
INNER JOIN
(
    SELECT ProductID, SUM(StatusCD <> 'O') AS OpenCount
    FROM yourTable
    GROUP BY ProductID
) t2
    ON t2.ProductID = t1.ProductID
ORDER BY
    t1.Date;

Demo

user_18632290
Related