Listing Years based on range

Question

Post reply

Listing Years based on range

born2achieve

Hall of Fame

Points: 3182
More actions
October 18, 2013 at 8:10 pm

#300290

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.

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

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

michael.ansaldi@gmail.com SSCommitted Points: 1701 More actions · Answer 1

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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;

born2achieve Hall of Fame Points: 3182 More actions · Answer 3

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 Points: 442467 More actions · Answer 4

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.

born2achieve Hall of Fame Points: 3182 More actions · Answer 5

born2achieve

Hall of Fame

Points: 3182

October 19, 2013 at 7:51 am

#1659916

Thank you lynn.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

born2achieve Hall of Fame Points: 3182 More actions · Answer 7

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.

born2achieve Hall of Fame Points: 3182 More actions · Answer 8

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.

michael.ansaldi@gmail.com SSCommitted Points: 1701 More actions · Answer 9

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.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

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.

born2achieve Hall of Fame Points: 3182 More actions · Answer 13

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.