Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Listing Years based on range Expand / Collapse
Author
Message
Posted Friday, October 18, 2013 8:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
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.


Post #1506357
Posted Saturday, October 19, 2013 12:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:55 PM
Points: 190, Visits: 389
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

Post #1506366
Posted Saturday, October 19, 2013 3:34 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:35 PM
Points: 22,992, Visits: 31,471
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 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)
Post #1506377
Posted Saturday, October 19, 2013 7:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
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.
Post #1506395
Posted Saturday, October 19, 2013 7:47 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:35 PM
Points: 22,992, Visits: 31,471
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 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)
Post #1506398
Posted Saturday, October 19, 2013 7:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
Thank you lynn.
Post #1506400
Posted Saturday, October 19, 2013 5:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1506426
Posted Saturday, October 19, 2013 7:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
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.
Post #1506427
Posted Saturday, October 19, 2013 7:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
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.
Post #1506428
Posted Saturday, October 19, 2013 10:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:55 PM
Points: 190, Visits: 389
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.
Post #1506431
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse