Posted by Erland Sommarskog on 02/07/06 00:33

CK (c_kettenbach@hotmail.com) writes:
> Okay, given my newness to SQL, and the complexity of this query, I
> thought I'd run this by you for your opinion:
>...
> 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..

Really why it takes longer with that clause I cannot tell, as I don't
know its tables nor its indexes. However, I found a simplification of
the query:


SELECT a.WeekEnding, b.lastdate, b.numlate, b.totaldate
FROM Accomplishment a
LEFT JOIN (SELECT weekending,
COUNT(weekending) AS totaldate,
SUM(CASE WHEN entrydate > weekending
THEN 1
ELSE 0
END) AS numlate,
MAX(CASE WHEN entrydate > weekending
THEN entrydate
END) AS lastdate
FROM Accomplishment
WHERE EmployeeID = 50
GROUP BY weekending) b ON a.WeekEnding = b.weekending
ORDER BY a.WeekEnding


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]