Here's a cte based solution.

But, as already mentioned, this query is everything but efficient and clean code.

From my point of view there are three options:

a) get the MONTHY column into a datetime format, as already recommended or

b) at least add a computed persisted column that'll do the same while keeping the "nasty column"

c) live with what you have and be prepared for date conversion errors (e.g. MONTHY =152013) or at the very least invalid date values.

I'd go with option (a).

declare @tbl table

(

emp int ,

monthyear int,

amount money

)

INSERT INTO @tbl

SELECT 1, 102013 ,1000

UNION ALL SELECT 2, 102013, 1000

UNION ALL SELECT 1, 112013 ,1000

UNION ALL SELECT 1, 112013, 1000

UNION ALL SELECT 2, 122013 ,1000

UNION ALL SELECT 2 ,122013,1000

UNION ALL SELECT 1, 12014 ,1000;

WITH cte AS

(

SELECT

CAST(RIGHT(monthyear,4) + LEFT(1000000 + monthyear,2) + '01' AS DATE) AS YYYYMMDD,

emp,

amount

FROM @tbl

)

SELECT emp, SUM(amount) AS TotalAmount

FROM cte

WHERE YYYYMMDD >='20131101' AND YYYYMMDD <'20141001'

GROUP BY emp