ChrisM@Work (8/16/2013)
My mistake, coding before thinking. Try this:
SELECT *FROM Calendar c
CROSS JOIN employee e
LEFT JOIN attendlog a
ON a.eid = e.eid
AND a.CheckTIme = c.Date
WHERE c.[Date] >= CAST('20130801' AS DATETIME)
AND c.[Date] <= CAST('20130815' AS DATETIME)
ORDER BY e.eid
thanks but i pass that level 🙂 now i am getting problem in another level
this is the query
SELECT
distinct w.eID,
w.status,
COALESCE(al.CheckTime, c.[Date]) AS [Date]
FROM(
SELECTeID,status
FROMdbo.AttendLog
GROUP BYeID,status
) AS w
CROSS JOINdbo.Calendar AS c
LEFT JOINdbo.AttendLog AS al ON al.eID = w.eID
AND DATEDIFF(DAY, al.CheckTime, c.[Date]) = 0
where date='20130731'
after execute query data show like this
EID--------status---------Date
17028--------O-----2013-07-31 08:11:00.000
204----------O-----2013-07-31 15:45:00.000
25416--------O-----2013-07-31 16:02:00.000
2634---------I-----2013-07-31 00:00:00.000
26318--------O-----2013-07-31 00:00:00.000
1312---------O-----2013-07-31 00:00:00.000
how i show result like this
EID--------status---------Date---------------Attendance
17028--------O-----2013-07-31 08:11:00.000-----P
204----------O-----2013-07-31 15:45:00.000-----P
25416--------O-----2013-07-31 16:02:00.000-----P
2634---------I-----2013-07-31 00:00:00.000-----A
26318--------O-----2013-07-31 00:00:00.000-----A
1312---------O-----2013-07-31 00:00:00.000-----A
means if date its not showing time then attendance is A if date show
time the attendance P
actulay this work is above my level thats why i am getting complexity 🙂
immad