Ford Fairlane (1/15/2014)
Refined and resolved !!!Thanks for all your input.
This is the working query.
SELECT
YEAR(CAST (a.ENDDATE AS DATETIME)),
MONTH(CAST (a.ENDDATE AS DATETIME)),
(
SELECT
SUM(b.DEPTH)
FROM
HOLELOCATION b
WHERE
b.HOLEID LIKE 'RHRC%'
AND ( CAST(YEAR(CAST(b.ENDDATE AS DATETIME)) AS VARCHAR) + RIGHT('00'+ CAST( MONTH(CAST (b.ENDDATE AS DATETIME)) AS VARCHAR(2)),2)) <= ( CAST(YEAR(CAST(a.ENDDATE AS DATETIME)) AS VARCHAR) + RIGHT('00'+ CAST( MONTH(CAST (a.ENDDATE AS DATETIME)) AS VARCHAR(2)),2))
AND YEAR(CAST (b.ENDDATE AS DATETIME)) IS NOT NULL AND MONTH(CAST (b.ENDDATE AS DATETIME)) IS NOT NULL
) AS DEPTH
FROM
HOLELOCATION a
WHERE a.HOLEID LIKE 'RHRC%' AND a.ENDDATE IS NOT NULL
GROUP BY
YEAR(CAST (a.ENDDATE AS DATETIME)),
MONTH(CAST (a.ENDDATE AS DATETIME))
ORDER BY
YEAR(CAST (a.ENDDATE AS DATETIME)) ASC,
MONTH(CAST (a.ENDDATE AS DATETIME)) ASC
That will certainly work but be advised that it's not scalable. If you end up with a not so large number of rows, it will eat the face off your server because it uses a thing known as a "Triangular Join", which is about half as bad as a full Cartesian Join (also known as a "Square Join").
Please see the following article on Triangular Joins.
http://www.sqlservercentral.com/articles/T-SQL/61539/
--Jeff Moden
Change is inevitable... Change for the better is not.