By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
With tbl(Date_, Code, Duration) As (
Select '4/2/23' As Date_, 'AB' As Code, 510 As Duration from dual Union All
Select '4/2/23', 'SH', 510 from dual Union All
Select '4/2/23', 'B2', 15 from dual Union All
Select '4/2/23', 'AB', 0 from dual Union All
Select '4/2/23', 'SH', 510 from dual Union All
Select '4/2/23', 'B2', 15 from dual ),
t as
(
select tb.*,
count(*) over (partition by date_, code, Duration) cnt,
min(Duration) over (partition by date_, code) min_dur
from tbl tb
)
select t.date_, t.code, t.duration,
case
when min_dur = 0 or -- there is a 0 duration
max(cnt) over (partition by date_, code) > 1 -- when there are the same durations in multiple rows.
then 'Y' else 'N'
end as flg
from t
| DATE_ | CODE | DURATION | FLG |
|---|---|---|---|
| 4/2/23 | AB | 0 | Y |
| 4/2/23 | AB | 510 | Y |
| 4/2/23 | B2 | 15 | Y |
| 4/2/23 | B2 | 15 | Y |
| 4/2/23 | SH | 510 | Y |
| 4/2/23 | SH | 510 | Y |