Hi and welcome to the forums. It is generally preferred if you can post ddl and sample data in a consumable format. Since you are brand new around here I did this for you so you can see an example. You can take a look at the first link in my signature for all the best practices when posting questions.
create table #Something
select 'G700052', 1, '2011-01-01', '2015-12-31 00:00:00.000' union all
select 'G700063', 1, '2011-01-01', '2015-12-31 00:00:00.000' union all
select 'G700089', 1, '2012-01-01', '2016-12-31 00:00:00.000' union all
select 'G800154', 1, '2013-01-01', '2017-12-31 00:00:00.000' union all
select 'LU100022', 37, '2012-02-03', '2016-12-31 00:00:00.000'
You don't need a recursive cte or any kind of looping mechanism for this type of thing. You need to use a tally table. You can read more about the tally table here.http://www.sqlservercentral.com/articles/62867/
I like to use a cte for a tally table because it is lightning fast.
Something like this.
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
from #Something s
join cteTally t on t.N >= YEAR(start_date) and t.N <= YEAR(End_Date)
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter
.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)