SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Listing Years based on range


Listing Years based on range

Author
Message
born2achieve
born2achieve
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 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
AmarettoSlim
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 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 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
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38853 Visits: 38508
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;




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
born2achieve
born2achieve
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 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
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38853 Visits: 38508
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
born2achieve
born2achieve
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 474
Thank you lynn.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84359 Visits: 41061
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.
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 problems
How to post performance problems
Forum FAQs
born2achieve
born2achieve
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 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()) - 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
born2achieve
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 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
AmarettoSlim
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 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 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search