If you don't already have a calendar table in your database, create one with the dates of interest like this:Create a calendar table first, if you don't have one already like this:CREATE TABLE #Calendar(Dt DATETIME NOT NULL PRIMARY KEY);;WITH cte AS( SELECT CAST('20130101' AS DATETIME) AS c UNION ALL SELECT DATEADD(dd,1,c) FROM cte WHERE c < '20131231')INSERT INTO #Calendar SELECT c FROM cte OPTION (MAXRECURSION 0);
Then query against that table. For example:SELECT c.Dt, o.Office_IDFROM #Calendar c CROSS JOIN Office_Location o WHERE NOT EXISTS( SELECT * FROM Emp_Attendance ea INNER JOIN Employee e ON e.Emp_ID = ea.EmpID WHERE o.Office_ID = e.Office_ID AND ea.Date = c.Dt);