Ford Fairlane (1/19/2014)
Thanks for the heads up Jeff, how would you tackle it then ?
Start by reducing the noise to see what's going on:
SELECT
x.YearMonth,
[DEPTH] = (
SELECT
SUM(b.DEPTH)
FROM HOLELOCATION b
CROSS APPLY (SELECT [YearMonth] = CONVERT(CHAR(6),CAST(b.ENDDATE AS DATETIME), 112)) x2
WHERE b.HOLEID LIKE 'RHRC%'
AND x2.YearMonth <= x.YearMonth
)
FROM HOLELOCATION a
CROSS APPLY (SELECT [YearMonth] = CONVERT(CHAR(6),CAST(a.ENDDATE AS DATETIME), 112)) x
WHERE a.HOLEID LIKE 'RHRC%'
AND x.YearMonth IS NOT NULL
GROUP BY x.YearMonth
ORDER BY x.YearMonth
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