## Listing Years based on range

 Author Message born2achieve SSCommitted Group: General Forum Members Points: 1540 Visits: 474 Hi, set @start = year(getdate()) - 15 set @Upto = year(getdate()) - 100 output has to be from 1998 ........ 1913 i wanted to display the years between these two ranges. I am able to do using while loop. Is there any way to do this without loop. if yes please show me some sample. AmarettoSlim SSC Eights! Group: General Forum Members Points: 917 Visits: 539 Can I ask why you don't want a loop? Recursion via a common table expression (CTE) can be your friend in this case.`DECLARE @fromYear DATETIME, @toYear DATETIMESELECT @fromYear='1913-01-01', @toYear='1998-01-01' WITH YearSequence (Year) as( SELECT @fromYear AS Year UNION ALL SELECT DATEADD(YEAR, 1, Year) FROM YearSequence WHERE Year < @toyear) SELECT Year FROM YearSequence ORDER BY 1 DESC` Lynn Pettis SSC Guru Group: General Forum Members Points: 224432 Visits: 40419 No recursion needed:`declare @Start int = year(getdate()) - 15;declare @Upto int = year(getdate()) - 100;with eTally(n) as (select top(@Start - @Upto + 1) ROW_NUMBER() over (order by (select null)) - 1 from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n) cross join (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt1(n))select @Start - n from eTally;` Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) born2achieve SSCommitted Group: General Forum Members Points: 1540 Visits: 474 wow, thanks a lot lynn and SSC.Hi lynn,I would like to understand your concept. Is it possible to give me brief explanation about your logic would be great. Lynn Pettis SSC Guru Group: General Forum Members Points: 224432 Visits: 40419 born2achieve (10/19/2013)wow, thanks a lot lynn and SSC.Hi lynn,I would like to understand your concept. Is it possible to give me brief explanation about your logic would be great.Go down to the fourth link in my signature block regarding Tally tables. Read that article. It will tell you all you want to know about tally tables. What I did in my code is create a dynamic tally table as a CTE. Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) born2achieve SSCommitted Group: General Forum Members Points: 1540 Visits: 474 Thank you lynn. Jeff Moden SSC Guru Group: General Forum Members Points: 508493 Visits: 44285 AmarettoSlim (10/19/2013)Can I ask why you don't want a loop? Recursion via a common table expression (CTE) can be your friend in this case.`DECLARE @fromYear DATETIME, @toYear DATETIMESELECT @fromYear='1913-01-01', @toYear='1998-01-01' WITH YearSequence (Year) as( SELECT @fromYear AS Year UNION ALL SELECT DATEADD(YEAR, 1, Year) FROM YearSequence WHERE Year < @toyear) SELECT Year FROM YearSequence ORDER BY 1 DESC`Because of the extremely low rowcount, you can't actually see the insidious problem with CTE's that count. Please see the following article...http://www.sqlservercentral.com/articles/T-SQL/74118/Also, your code didn't actually run right the first time I tried to run it because of missing semi-colons. You might also want to get out of the habit of using ORDER BY on a column ordinal because that method has been deprecated.As for why you might want to avoid a loop, do you have a good reason for why you'd want to intentionally write slower code when faster code is easily available and usually easier to write? --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs born2achieve SSCommitted Group: General Forum Members Points: 1540 Visits: 474 Hi Jeff Thanks for your response and great article. But i have a concern that you are using custom year next to the declaration.SELECT @fromYear='1913-01-01', @toYear='1998-01-01'I don't want this to be hard coded. because the range applied on the formula may change some point of time, The current formula set @start = year(getdate()) - 15set @Upto = year(getdate()) - 100Here 15, 100 may get changed in near future. so i don't want this to be card coded. is there any way to avoid this to be hard coded. born2achieve SSCommitted Group: General Forum Members Points: 1540 Visits: 474 Also, none of the above queries executing correctly. i tried to check the query is working fine or not using http://sqlfiddle.com. Somethign wrong with the declaration. could you please suggest me what's wrong with the queries. AmarettoSlim SSC Eights! Group: General Forum Members Points: 917 Visits: 539 Jeff Moden (10/19/2013)AmarettoSlim (10/19/2013)Can I ask why you don't want a loop? Recursion via a common table expression (CTE) can be your friend in this case.`DECLARE @fromYear DATETIME, @toYear DATETIMESELECT @fromYear='1913-01-01', @toYear='1998-01-01' WITH YearSequence (Year) as( SELECT @fromYear AS Year UNION ALL SELECT DATEADD(YEAR, 1, Year) FROM YearSequence WHERE Year < @toyear) SELECT Year FROM YearSequence ORDER BY 1 DESC`Because of the extremely low rowcount, you can't actually see the insidious problem with CTE's that count. Please see the following article...http://www.sqlservercentral.com/articles/T-SQL/74118/Also, your code didn't actually run right the first time I tried to run it because of missing semi-colons. You might also want to get out of the habit of using ORDER BY on a column ordinal because that method has been deprecated.As for why you might want to avoid a loop, do you have a good reason for why you'd want to intentionally write slower code when faster code is easily available and usually easier to write?Thanks for sharing the article, Jeff. I learn something new everyday and this tops the list for past 24 hours.born2achieve, don't use my example. Take a few minutes to read the article Jeff wrote, its extremely evident that there are better methods available such as a tally table.