immaduddinahmed (8/15/2013)
from attendlog aleft outer join employee e on a.eid = e.eid
If an employee has no attendance record for the selected time period then they will be excluded from the output. Either reverse the join (from employee left outer join attendlog) or, better still, use a full outer join.
where CONVERT (DATETIME,CONVERT (VARCHAR,CheckTIme,101))>'20130801' and CONVERT (DATETIME,CONVERT (VARCHAR,CheckTIme,101))<'20130815'
If you convert your constants to match the datatype of your columns, then SQL Server won’t have to do a full table scan of 10 years to pick up a single day’s worth of data.
You could process the attendlog table to provide a set of distinct dates and left join a set containing a product of both tables to it. You could also use a calendar table in the same way, restricting dates in the same way as you are at present with the attendlog table. Either way, you will have the “missing dates”. Something like
SELECT *
FROM Calendar c
LEFT JOIN (
SELECT
eid = COALESCE(a.eid,e.eid),
a. CheckTIme
FROM employee e
FULL OUTER JOIN attendlog a
ON a.eid = e.eid
) a
ON a. CheckTIme = c.Date
WHERE c.Date > CAST('20130801' AS DATETIME) AND c.Date < CAST(‘20130815’ AS DATETIME)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden