sql server - Recursive References are not allowed in Sub_queries T-SQL -

i have cte query so:

declare @stop datetime set @stop='2014-12-12 00:00:00.000'  declare @start datetime set @start='2011-12-12 00:00:00.000'  declare @temp datetime set @temp=@start   x(a,b) as(      select @temp a,count(*) b           table1                left(convert(varchar,table1row1,120),4)=left(convert(varchar,@temp,120),4)       union select dateadd(year,1,(select x.a x)) a,count(*) b           table1                left(convert(varchar,table1row1,120),4)=left(convert(varchar,(select x.a x),120),4)                , datediff(yyyy,(select x.a x),@stop)>0)       select a,b #temptbl1 x option(maxrecursion 32767); 

i'm trying count of rows each year @start @stop. query accepts select x.a x @ select statement not @ clause.

i'm getting compile time error stating: recursive member of common table expression 'x' has multiple recursive references.

on executing, i'm getting error recursive references not allowed in sub-queries. but, i've referenced in select query shows no error. there syntactical problem, or not allowed reference table there?

select dateadd(year,1,(select x.a x)) a,count(*) b           table1                left(convert(varchar,table1row1,120),4)=left(convert(varchar,(select x.a x),120),4)                , datediff(yyyy,(select x.a x),@stop)>0 

you select x 3 times in query. try doing join on table1 , x instead of 3 subquery's.

also think query can done allot easier. like:

select datepart(year, datecolumn), count(pk) table1 datecolumn between @startdate , @enddate group datepart(year, datecolumn)