I don't have any other table. from this table can i find date absent for all employee in one query. For example in the above table empno 1 is present on 1/1/2010,2/1/2010 and 3/1/2010. He is absent for all days except these three days. how to get the all absent dates for all employee. can we find all dates absent for emp 1,2,3 in one query
SELECT p.empno,p.[Date]
FROM
(
SELECT t.empno,f.[Date]
FROM dbo.CalendarTable('20100101','20100131',1)f
CROSS JOIN (SELECT DISTINCT empno FROM YourTable) t
)p
LEFT JOIN YourTable q
ON q.entrydate = p.[Date]
AND q.empno = p.empno
WHERE q.empno IS NULL
the code for function CalendarDate can be found below
Finding missing dates from the table for all emp
Posted - 11/21/2010 : 12:37:41
