ChrisM@Work (3/14/2013)
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 😉
Cool, need to modify my code as I managed miss the WHERE CLAUSE.