Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Listing Years based on range Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, October 18, 2013 8:10 PM
 SSC Veteran Group: General Forum Members Last Login: Thursday, September 3, 2015 11:06 PM Points: 204, 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.
Post #1506357
 Posted Saturday, October 19, 2013 12:07 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, December 2, 2015 3:24 PM Points: 195, 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 DATETIMESELECT @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 Group: General Forum Members Last Login: Today @ 6:24 AM Points: 23,522, Visits: 37,765
 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;`
Post #1506377
 Posted Saturday, October 19, 2013 7:16 AM
 SSC Veteran Group: General Forum Members Last Login: Thursday, September 3, 2015 11:06 PM Points: 204, 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.
Post #1506395
 Posted Saturday, October 19, 2013 7:47 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 6:24 AM Points: 23,522, Visits: 37,765
 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.
Post #1506398
 Posted Saturday, October 19, 2013 7:51 AM
 SSC Veteran Group: General Forum Members Last Login: Thursday, September 3, 2015 11:06 PM Points: 204, Visits: 474
 Thank you lynn.
Post #1506400
 Posted Saturday, October 19, 2013 5:35 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 4:15 PM Points: 42,082, Visits: 39,476
 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 DATETIMESELECT @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." Helpful Links:How to post code problemsHow to post performance problems
Post #1506426
 Posted Saturday, October 19, 2013 7:05 PM
 SSC Veteran Group: General Forum Members Last Login: Thursday, September 3, 2015 11:06 PM Points: 204, 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()) - 15set @Upto = year(getdate()) - 100Here 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 Veteran Group: General Forum Members Last Login: Thursday, September 3, 2015 11:06 PM Points: 204, 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.
Post #1506428
 Posted Saturday, October 19, 2013 10:05 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, December 2, 2015 3:24 PM Points: 195, 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 DATETIMESELECT @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

 Permissions