Posted by Erland Sommarskog on 02/07/06 01:18

Seeing Hugo's queries, I realize that I did a blunder when I cut
DISTINCT. Assuming that you want all weekendings - also those when
Employee 50 did not enter anything at all, this may be better:

SELECT a.WeekEnding, b.lastdate, b.numlate, b.totaldate
FROM (SELECT DISTINCT WeekEnding FROM Accomplishment) AS 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]