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..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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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|||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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> What I'm trying to do is for each pay period find which of the employees submitted a timesheet and which they were late (and if they were late, how many of them). <<
Where is the payperiod table in your data model??
>> No idea why that makes such a difference.. <<
Because your schema design is a pile of crap?? Duh!
Wild Idea! for uou Please post DDL, so that people do not have to
guess what the keys, constraints, Declarative Referential Integrity,
data types, etc. in your schema are. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it.
Newbies think that DDL is not as important as DML. They assume they
can "repair" a bad schema in code. You cannot. Ever. The best you can
hope for is a horrible nested sert of joins, like Sommarskog posted.
Do you want ot do it right or just kludge and patch it?
