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