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

Question

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

Answer 1

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

Answer 2

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)