SQL query to find dates

Question

I wrote an SQL query that looks for an employee table for members who are absent. But how can I show the dates that I mean the date of absence of the period? It works great except for the date. I want to show on what day of this absent period? I can not show the date.

Example:

BadgeNumber - EmployeeName - Absent Date
10042 - Mr. Erik - 2014-07-01

The code:

SELECT  SMEmployee.BadgeNumber,SMEmployee.EmployeeFullName,SMDepartment.DepartmentName
    FROM SMEmployee
    LEFT OUTER JOIN SMDepartment 
    ON SMDepartment.DepartmentID=SMEmployee.DepartmentID    
    WHERE EmployeeID NOT IN (
    SELECT empCheckInOut.USERID 
    FROM empCheckInOut 
    WHERE convert(date,CHECKTIME)='2014-07-01')

Answer 1

Usually, to check if a record exists (how you do it to participate, the employee’s check was not checked during the day), it’s more efficient to use LEFT OUTER JOIN and check for NULL in one of the WHERE clause fields (i.e. LEFT JOIN, wherever single row not found).

However, this also will not give you a date.

, , , , .

, , , , CROSS JOIN, LEFT JOIN empCheckInOut. .

SELECT  SMEmployee.BadgeNumber,SMEmployee.EmployeeFullName,SMDepartment.DepartmentName, subDate.aDate
FROM SMEmployee
CROSS JOIN (SELECT '2014-07-01' AS aDate UNION SELECT '2014-06-30') subDate
LEFT OUTER JOIN SMDepartment 
ON SMDepartment.DepartmentID=SMEmployee.DepartmentID    
LEFT OUTER JOIN empCheckInOut
ON SMEmployee.EmployeeID = empCheckInOut.USERID 
AND convert(date,CHECKTIME) = subDate.aDate
WHERE empCheckInOut.USERID IS NULL

Answer 2

, , "empCheckInOut".

, 1 , , "empCheckInOut".

. #temp 06-01-2014, 06-02-2014, 06-03-2014,... ( 30 )

CheckInOut 06-01-2014, 06-03-2014,... (emp 2 , 29 )

, , .