Easiest way to do this is using a calendar table. If you don't have one in your database, create one using the code below. CREATE TABLE #Calendar (Dt DATETIME);DECLARE @startDate DATETIME, @endDate DATETIME;SET @startDate = '20110101';SET @endDate = '20110131';;WITH cte AS( SELECT @startDate AS Dt UNION ALL SELECT DATEADD(dd,1,dt) FROM cte WHERE dt < @endDate)INSERT INTO #Calendar SELECT Dt FROM cte;
Then join the calendar table like shown below.;WITH e AS ( SELECT DISTINCT EmpNo FROM YourTable)SELECT e.EmpNo, c.DtFROM e CROSS JOIN #Calendar c LEFT JOIN YourTable y ON y.EmpNo = e.EmpNo AND y.Dt = c.DtWHERE y.Dt IS NULL;