Filtering record on More than One Condition

Question

I have the given table structure

Attendance Table
Id int
AttDate datetime
EmpId int
WorkHrs decimal
ClockedIn string
ClockedOut string
HolidayHrs decimal
DayOffHrs decimal
MatHrs decimal
WorkHomeHrs decimal

I have to select all the record where ClockedIn or ClockedOut is null only if
either holidayhrs >= WorkHrs or DayOffHrs >= WorkHrs or MatHrs >= workHrs or WorkHomeHrs >= WorkHrs .
Please help with proper sql to filter the record who are not clockedin and clockedout

!

I have the following record as attached here

The Result should be

Answer 1

You should provide data in a consumable format that we can use to test and validate a solution. Here is an example:

Declare @Attendance Table (
        Id int Identity(1,1) Primary Key 
      , AttDate datetime
      , EmpId int
      , WorkHrs decimal(3,1)
      , ClockedIn char(5)
      , ClockedOut char(5)
      , HolidayHrs decimal(3,1)
      , DayOffHrs decimal(3,1)
      , MatHrs decimal(3,1)
      , WorkHomeHrs decimal(3,1));

 Insert Into @Attendance (AttDate, EmpId, WorkHrs, ClockedIn, ClockedOut, HolidayHrs, DayOffHrs, MatHrs, WorkHomeHrs)
 Values ('2021-01-01', 1, 8.3,    Null,    Null,  8.3, Null, Null, Null)
      , ('2021-01-01', 2, 7.3,    Null,    Null,  7.3, Null, Null, Null)
      , ('2021-01-01', 3, 7.3,    Null,    Null,  7.3, Null, Null, Null)
      , ('2021-01-01', 4, 8.3,    Null,    Null,  8.3, Null, Null, Null)
      , ('2021-01-02', 1, 8.3, '09:00', '17:00', Null, Null, Null, Null)
      , ('2021-01-02', 2, 7.3, '09:30', '17:30', Null, Null, Null, Null)
      , ('2021-01-02', 3, 7.3,    Null,    Null, Null,  7.3, Null, Null)
      , ('2021-01-02', 4, 8.3, '17:00',    Null, Null, Null, Null, Null)
      , ('2021-01-03', 1, 8.3, '08:00',    Null, Null, Null, Null, Null)
      , ('2021-01-03', 2, 7.3,    Null,    Null, Null, Null,  7.3, Null)
      , ('2021-01-03', 3, 7.3,    Null,    Null, Null, Null, Null, Null)
      , ('2021-01-03', 4, 8.3, '09:30', '12:00', Null, Null, Null, Null)
      , ('2021-01-03', 1, 8.3, '17:30',    Null, Null, Null, Null, Null)
      , ('2021-01-03', 2, 7.3, '08:30',    Null, Null, Null, Null, Null)
      , ('2021-01-03', 3, 7.3, '08:30',    Null, Null, Null, Null, Null)
      , ('2021-01-03', 4, 8.3,    Null,    Null, Null, Null, Null,  8.3);

Your description does not match the desired results...

EmpId = 1, AttDate = 2021-01-01: HolidayHrs are equal to WorkHrs but that row is not included in your desired results. Based on your rules - all rows should be returned.

Can you explain your desired results further?

Answer 2

The output sample seems not to match the description of the conditions. Here is my guess based on the source data and outputs:

SELECT * 
FROM Attendance
WHERE (ClockedIn IS NULL OR ClockedOut IS NULL) 
AND HolidayHrs IS NULL AND DayOffHrs IS NULL AND MatHrs IS NULL AND WorkHomeHrs IS NULL;

Answer 3

Here is another solution

 Select *
   From @Attendance a
  Where Exists (Select *
                  From @Attendance a2
                 Where a2.Id = a.Id
                   And a2.ClockedIn Is Null 
                   And coalesce(a.HolidayHrs, a.DayOffHrs, a.MatHrs, a.WorkHomeHrs) Is Null)
     Or (a.ClockedOut Is Null And a.ClockedIn Is Not Null);

Answer 4

Hi @Polachan Paily ,

Welcome to Microsoft Q&A!

Please refer below:

create table Attendance (  
Id int Identity(1,1) Primary Key   
, AttDate datetime  
, EmpId int  
, WorkHrs decimal(3,1)  
, ClockedIn char(5)  
, ClockedOut char(5)  
, HolidayHrs decimal(3,1)  
, DayOffHrs decimal(3,1)  
, MatHrs decimal(3,1)  
, WorkHomeHrs decimal(3,1));  
      
Insert Into Attendance (AttDate, EmpId, WorkHrs, ClockedIn, ClockedOut, HolidayHrs, DayOffHrs, MatHrs, WorkHomeHrs)  
Values ('2021-01-01', 1, 8.3,    Null,    Null,  8.3, Null, Null, Null)  
, ('2021-01-01', 2, 7.3,    Null,    Null,  7.3, Null, Null, Null)  
, ('2021-01-01', 3, 7.3,    Null,    Null,  7.3, Null, Null, Null)  
, ('2021-01-01', 4, 8.3,    Null,    Null,  8.3, Null, Null, Null)  
, ('2021-01-02', 1, 8.3, '09:00', '17:00', Null, Null, Null, Null)  
, ('2021-01-02', 2, 7.3, '09:30', '17:30', Null, Null, Null, Null)  
, ('2021-01-02', 3, 7.3,    Null,    Null, Null,  7.3, Null, Null)  
, ('2021-01-02', 4, 8.3, '17:00',    Null, Null, Null, Null, Null)  
, ('2021-01-03', 1, 8.3, '08:00',    Null, Null, Null, Null, Null)  
, ('2021-01-03', 2, 7.3,    Null,    Null, Null, Null,  7.3, Null)  
, ('2021-01-03', 3, 7.3,    Null,    Null, Null, Null, Null, Null)  
, ('2021-01-03', 4, 8.3, '09:30', '12:00', Null, Null, Null, Null)  
, ('2021-01-03', 1, 8.3, '17:30',    Null, Null, Null, Null, Null)  
, ('2021-01-03', 2, 7.3, '08:30',    Null, Null, Null, Null, Null)  
, ('2021-01-03', 3, 7.3, '08:30',    Null, Null, Null, Null, Null)  
, ('2021-01-03', 4, 8.3,    Null,    Null, Null, Null, Null, 8.3);  

select * from (  
 select * from Attendance  
 except  
 select * from Attendance  
where holidayhrs >= WorkHrs or DayOffHrs >= WorkHrs   
 or MatHrs >= workHrs or WorkHomeHrs >= WorkHrs) a  
 where a.ClockedIn is null or a.ClockedOut is null  

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Hi @Polachan Paily ,

Please also refer below:

Method 2:

select * from Attendance  
where id not in (select id from Attendance where  
 holidayhrs >= WorkHrs or DayOffHrs >= WorkHrs   
 or MatHrs >= workHrs or WorkHomeHrs >= WorkHrs)  
 and (ClockedIn is null or ClockedOut is null)  

Method 3:

 select * from Attendance a  
where  not exists (select * from Attendance b where  
 (holidayhrs >= WorkHrs or DayOffHrs >= WorkHrs   
 or MatHrs >= workHrs or WorkHomeHrs >= WorkHrs) and b.id=a.id )  
 and (ClockedIn is null or ClockedOut is null)  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.