Posted by CK on 02/06/06 19:11

Okay, given my newness to SQL, and the complexity of this query, I thought I'd
run this by you for your opinion:



SELECT DISTINCT a.WeekEnding, c.lastdate, c.numlate, b.totaldate

FROM Accomplishment a LEFT OUTER JOIN

(SELECT weekending, COUNT(weekending) AS
totaldate

FROM Accomplishment

WHERE (EmployeeID = 50)

GROUP BY weekending) b ON a.WeekEnding =
b.weekending LEFT OUTER JOIN

(SELECT weekending, MAX(entrydate) AS
lastdate, COUNT(weekending) AS numlate

FROM accomplishment

WHERE employeeid = 50 AND entrydate >
weekending

GROUP BY weekending) c ON a.WeekEnding =
c.weekending

ORDER BY a.WeekEnding



What I'm trying to do is for each pay period find which ones the employee
submitted a timesheet and which they were late (and if they were late, how
many of them). However, the query takes a good 5 seconds, and it seems
removing the "entrydate > weekending" clause speeds things up to almost
instant, however it does ruin the count that I really want. No idea why
that makes such a difference..

[Back to original message]