The easiest way to do this is to use a JOIN to filter the data.
I wasn't going to wade through your code to try and work out your table schemas so I created a dummy set of tables to demonstrate the point :
create table Employee (EmpId int identity(1,1), Name nvarchar(125))
insert into Employee values ('Joe Bloggs')
Create Table attendanceLog
(
id int identity(1,1),
EmpId int,
DateLogged Date
)
insert into attendanceLog values
(1, '2013-10-02'),
(1, '2013-10-03'),
(1, '2013-10-04'),
(1, '2013-10-05'),
(1, '2013-10-06'),
(1, '2013-10-07'),
(1, '2013-10-08')
The following query will then list the dates from (your) @DateT table that do not appear in (my) AttendanceLog table:
select D
from @DateT D
left outer JOIN attendanceLog AL ON AL.DateLogged=D.D
WHERE EmpId IS NULL
Note I have used a
Date
column type. You appear to be using a lot of
CONVERT
functions in your code so you may need to change the
ON
clause to
ON CAST(AL.DateLogged AS Date)=D.D