Filtering record on More than One Condition

Polachan Paily 221 Reputation points
2021-01-21T22:07:50.347+00:00

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
59262-capture.png

The Result should be
59158-image.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Jeffrey Williams 1,891 Reputation points
    2021-01-21T23:29:35.227+00:00

    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?

    0 comments No comments

  2. Guoxiong 8,126 Reputation points
    2021-01-22T15:33:18.743+00:00

    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;
    
    0 comments No comments

  3. Jeffrey Williams 1,891 Reputation points
    2021-01-22T16:00:01+00:00

    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);
    
    0 comments No comments

  4. MelissaMa-MSFT 24,176 Reputation points
    2021-01-22T02:20:11.913+00:00

    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:
    59377-output.png

    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.

    0 comments No comments

  5. MelissaMa-MSFT 24,176 Reputation points
    2021-01-22T02:40:11.757+00:00

    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.

    0 comments No comments