sqlnyc (3/12/2013)
I'm attempting to optimize some code that I was just handed, and I'm not exactly sure if what I want to do is possible. ...
I'd check that it's doing what you think it is before attepting to optimise it:
SELECT h.EmployeeID
,SUM(CASE WHEN h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount
ELSE 0
END) AS AmountPeriod
,SUM(CASE WHEN h.HeaderDate <= @ReportEndingDate THEN d.Amount
ELSE 0
END) AS AmountYtd
FROM Details d
INNER JOIN Header h ON d.HeaderID = h.HeaderID
INNER JOIN CodeIDTable1 c ON c.CodeID = d.CodeID
WHERE h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate
GROUP BY h.EmployeeID
Look at the WHERE clause and the two CASE expressions 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden