Posted by Hugo Kornelis on 02/07/06 00:52

On Mon, 06 Feb 2006 17:11:38 GMT, CK wrote:

>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..

Hi CK,

The query looks more complicated than it needs to be. Based on a whole
lot of assumptions about your data and without any testing (check out
www.aspfaq.com/5006 if you prefer less assumptions and more testing),
I'd suggest changing it to

SELECT WeekEnding,
MAX(CASE WHEN entrydate > weekending THEN entrydate END) AS
LastDate,
COUNT(CASE WHEN entrydate > weekending THEN 'CountMe' END) AS
NumLate,
COUNT(*) AS TotalDate
FROM Accomplishment
WHERE EmployeeId = 30
GROUP BY WeekEnding
ORDER BY WeekEnding

Or, if you want a report for all employees:

SELECT EmployeeID,
WeekEnding,
MAX(CASE WHEN entrydate > weekending THEN entrydate END) AS
LastDate,
COUNT(CASE WHEN entrydate > weekending THEN 'CountMe' END) AS
NumLate,
COUNT(*) AS TotalDate
FROM Accomplishment
GROUP BY EmployeeID,
WeekEnding
ORDER BY EmployeeID,
WeekEnding


--
Hugo Kornelis, SQL Server MVP

• Next in forum: Re: Searching/Estracting numerical data
• Prev in forum: Re: how do i replace a substring in sqlserver ?
• Thread view: Re: Any T-SQL advice?

[Reply to this message]