By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH yourTable AS (
SELECT '2021-01-01' AS Date, 'U1000' AS ProductID, 'I' AS StatusCD UNION ALL
SELECT '2021-01-10', 'U1000', 'O' UNION ALL
SELECT '2021-01-10', 'U2000', 'I' UNION ALL
SELECT '2021-01-15', 'U3000', 'O'
)
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;
| Date | ProductID | StatusCD | Label |
|---|---|---|---|
| 2021-01-01 | U1000 | I | 1 |
| 2021-01-10 | U1000 | O | 1 |
| 2021-01-10 | U2000 | I | 1 |
| 2021-01-15 | U3000 | O | 0 |