Return rows if the eeid and date only have one value from paycode id

Question

I'm trying to pull payid with time = 0 if there is no other payid's on the same date for eeid.

date	eeid	payid	time
10/01/2022	123	669	0
10/01/2022	123	146	10
10/01/2022	456	669	0
10/02/2022	456	669	0
10/03/2022	456	669	0
10/03/2022	456	146	10

The return should look like this:

date	eeid	payid	time0
10/01/2022	456	669	0
10/02/2022	456	669	0

I've tried doing different group bys and counts and just get stuck.

Answer 1

You can use not exists to filter out rows for which another row exists with the same (eeid, date) and a non-0 time.

select t.*
from mytable t
where t.time = 0 and not exists (
    select 1
    from mytable t1
    where t1.eeid = t.eeid and t1.date = t.date and t1.time > 0
)

Demo on DB Fiddle

Technically we don't even need to filter on time in the where clause, this is sufficient :

select t.*
from mytable t
where not exists (
    select 1
    from mytable t1
    where t1.eeid = t.eeid and t1.date = t.date and t1.time > 0
)