How to add Multiple Case when statement with where condition to get the Attendance Report

Polachan Paily 221 Reputation points
2020-10-14T09:39:07.037+00:00

I have a table EmpAttendance with the following structure
Create Table EmpAttendance

(Empid int, EmpName varchar(max), AttendanceDate datetime, IsAttendance bit, IsHoliday bit, IsSick bit, IsDayOff bit)
The Table has the following Record
INSERT INTO EmpAttendance
(Empid,EmpName,AttendanceDate,IsAttendance,IsHoliday,IsSick,
IsDayOff )
VALUES
(1,'AAAA','14-10-2020',0,1,0,0)
(2,'BBBB','14-10-2020',1,0,0,0)
(3,'CCCC','14-10-2020',1,1,0,0)
(4,'DDDD','14-10-2020',1,1,1,1)

(5,'EEEE','14-10-2020',0,0,0,1)

(6,'FFFF','14-10-2020',0,0,0,0)

I am trying to write the sql
declare @empIds varchar(max)
declare @Attendancetype varchar(max)
set @EmpIds = '1,3,4'
set @Attendancetype = 'NA,HO,SI'

Select * from EmpAttendance where EmpId in(@EmpIds)
And
Case When @Attendancetype contains 'NA' - All record must be displayed with the condition IsAttendance = 1
Case When @Attendancetype contains 'HO' - All record must be displayed with the condition IsHoliday= 1

Case When @Attendancetype contains 'SI' - All record must be displayed with the condition IsSick= 1
The List should be displayed as an example

(1,'AAAA','14-10-2020',0,1,0,0)
(2,'BBBB','14-10-2020',1,0,0,0)
(3,'CCCC','14-10-2020',1,1,0,0)
(4,'DDDD','14-10-2020',1,1,1,1)

Please help to build the sql to get the desired result

Thanks

Pol

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

Accepted answer
  1. Johan Kangasniemi 76 Reputation points
    2020-10-14T12:02:56.53+00:00

    I think this does what you're asking for.

    You would benefit from changing your approach to the data rather than using this. In particular, the EmpId in (@EmpId) isn't great. I added select cast(value as int) as empid into #empids from string_split(@empids,',') into my query to split your comma separated list into a table. You could of course just do an inner join to the newly created temp table instead.

    Select     Empid
              ,Empname
              ,AttendanceDate
              ,IsAttendance
              ,IsHoliday
              ,IsSick
              ,IsDayOff
    from     EmpAttendance 
    where     EmpId in (select EmpId from #EmpIds)
    and case when charindex('NA',@AttendanceType)>0 and IsAttendance = 1 then 1
             when charindex('HO',@AttendanceType)>0 and IsHoliday = 1 then 1
             when charindex('SI',@AttendanceType)>0 and IsSick = 1 then 1
             end = 1
    

    All in all, this is what my code looks like for what you've provided:

    Create Table EmpAttendance
    (Empid int, EmpName varchar(max), AttendanceDate datetime, IsAttendance bit, IsHoliday bit, IsSick bit, IsDayOff bit)
    
    INSERT INTO EmpAttendance
    (Empid,EmpName,AttendanceDate,IsAttendance,IsHoliday,IsSick,
    IsDayOff )
    VALUES
    (1,'AAAA', '2020-10-14',0,1,0,0)
    ,(2,'BBBB','2020-10-14',1,0,0,0)
    ,(3,'CCCC','2020-10-14',1,1,0,0)
    ,(4,'DDDD','2020-10-14',1,1,1,1)
    ,(5,'EEEE','2020-10-14',0,0,0,1)
    ,(6,'FFFF','2020-10-14',0,0,0,0)
    
    declare @empIds varchar(max)
    declare @Attendancetype varchar(max)
    set @EmpIds = '1,2,3,4,5,6'
    drop table if exists #empids;
    select cast(value as int) as empid into #empids from string_split(@empids,',')
    set @Attendancetype = 'NA,HO,SI'
    
    Select Empid,Empname,AttendanceDate,IsAttendance,IsHoliday,IsSick,IsDayOff
    from EmpAttendance where EmpId in (select EmpId from #EmpIds)
    and case when charindex('NA',@AttendanceType)>0 and IsAttendance = 1 then 1
             when charindex('HO',@AttendanceType)>0 and IsHoliday = 1 then 1
             when charindex('SI',@AttendanceType)>0 and IsSick = 1 then 1
             end = 1
    

1 additional answer

Sort by: Most helpful
  1. Viorel 110.1K Reputation points
    2020-10-14T12:33:19.07+00:00

    If string_split is available, then check this query too:

    select distinct e.* from EmpAttendance as e   
    cross apply string_split(@EmpIds,',') as i  
    cross apply string_split(@Attendancetype, ',') as a  
    where e.Empid = i.value  
    or (a.value = 'NA' and e.IsAttendance = 1)  
    or (a.value = 'HO' and e.IsHoliday = 1)  
    or (a.value = 'SI' and e.IsSick = 1)  
    
      
    
    0 comments No comments