SELECT

c.date,

a.eid,

a.checktime

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('20130731' AS DATETIME) AND c.Date < CAST('20130815' AS DATETIME)

well i use this query but its giving me this result

date-----------eid-------------checktime

2013-08-01----NULL-------------NULL

2013-08-02----NULL-------------NULL

2013-08-03----NULL-------------NULL

2013-08-04----NULL-------------NULL

2013-08-05----NULL-------------NULL

Thanks for the help