Click here to monitor SSC
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
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 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-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 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-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24227 Visits: 37978
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
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 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-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24227 Visits: 37978
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
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 474
Thank you lynn.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45163 Visits: 39924
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
born2achieve
born2achieve
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

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

Group: General Forum Members
Points: 230 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-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 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