# Listing Years based on range

• 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.

• 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 DATETIME`

`SELECT @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`

• 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;`

• 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.

• 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.

• Thank you lynn.

• 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 DATETIME`

`SELECT @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 Moden

RBAR 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.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• 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()) - 15

set @Upto = year(getdate()) - 100

Here 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.

• 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.

• 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 DATETIME`

`SELECT @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.

• born2achieve (10/19/2013)

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()) - 15

set @Upto = year(getdate()) - 100

Here 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.

Look again... it's NOT my code. I quoted someone else's code.

--Jeff Moden

RBAR 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.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• born2achieve (10/19/2013)

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.

Must be something wrong on your end. I just ran Lynn's code and it worked as expected according to what I perceive your specs were.

--Jeff Moden

RBAR 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.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• born2achieve (10/19/2013)

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()) - 15

set @Upto = year(getdate()) - 100

Here 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.

One would expect someone to take some initiative when provided free help. The code I provided was based specifically on your orininal post, as shown here:

born2achieve (10/18/2013)

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.

Now you are changing your specs and expecting the code provided to still work.

Your original spec starts with the Latest year (1998) going to the earlier year (1913). If you look at the logic I employed you would see how that works. Now that you want to flip it and start at the earlier year (1913) and go to the latest year (1998) you need to adjust the logic.

Perhaps it would be better if you told us what you are actually trying to achieve.

• Hi Lynn,

thanks for your reply. i haven't changed my requirement. anyhow apologize for the miss communication here.

What i told was about the code provided by ssc and not you. the ssc code has hard coded date set up for further use.

SELECT @fromYear='1913-01-01', @toYear='1998-01-01'

thats what i meant to say.

thanks jeff and lynn for your time on this post.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply