Is there a way to retrieve a interval of dates as rows ms-sql?

Question

I'm trying to do a query to discover which days an employee didn't work.

For example:

If I retrieve all the dates between 21/07 and 25/07 that the employee worked I will have something like that:

21/07/2016
22/07/2016
23/07/2016
25/07/2016

But I want the days that he didn't work. In this case it's 24/07.

I'm looking for a solution and my idea is to do a subquery retrieving an interval of dates, but as rows to do a not contains on the query above.

Example:

SELECT MyDate 
FROM MyTable
WHERE MyDate between 21-07 and 25-05
and MyDate not in 
**(the subquery here)**

Anyone can help?

Answer 1

Best way is with not exists and you need to query a table that has all the possible date values:

SELECT ALLDATECOLUMN FROM ALLDATES
WHERE 
NOT EXISTS (SELECT 1 FROM MYDATES WHERE MYDATES = ALLDATECOLUMN)
AND ALLDATECOLUMN BETWEEN 21-07 AND 25-05