Only show rows where certain data does not match

I have a table that contains if an employee has logged into work or not. A new row is inserted when they log in and when they log out. What I am wanting to do is result on the employees that have not logged out on todays date.

The table structure is as below:

Employee_Ref	ShiftDate	In_or_Out
191	2021-05-07 00:00:00.000	I
320	2021-05-07 00:00:00.000	I
320	2021-05-07 00:00:00.000	O

Result required would be:

Employee_Ref	ShiftDate	In_or_Out
191	2021-05-07 00:00:00.000	I

Could anyone help please?

Answer

What I am wanting to do is result on the employees that have not logged out on todays date.

Presumably, you want people who did log in on today’s date. I’ll assume that is the case.

You can use aggregation if you want employees:

select employee_ref
from t
where shiftdate >= current_date and
      shiftdate < current_date + interval '1 day'
group by employee_ref
having sum(case when in_or_out = 'O' then 1 else 0 end) = 0;

You can use not exists if you want the detailed rows:

select t.*
from t
where shiftdate >= current_date and
      shiftdate < current_date + interval '1 day' and
      not exists (select 1
                  from t t2
                  where t2.employee_ref = t.employee_ref and
                        t2.shiftdate >= current_date and
                        t2.shiftdate < current_date + interval '1 day' and
                        t2.in_or_out = 'O'
                 );

Note that date functions vary significantly among databases. This uses standard SQL syntax that can be adapted to your database.