Solved

Sql query for getting all years greater than 2003 and less than or equal to current year.

Posted on 2014-09-16
14
553 Views
Last Modified: 2014-09-16
Hi Experts.

I need SQL query for finding, all years which are greater than 2003 and less than or equal to Current Year

ans. 2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014

Note:  all the above years are not stored in any table of SQL Server.
0
Comment
Question by:Tapan Pattanaik
  • 4
  • 2
  • 2
  • +4
14 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Do you have any tables which are more than 2,000 rows in length?

Let's say you have the AdventureWorks2012 database. Person.Address is about 20,000 rows in length.

You can use this:


with mynums as (
SELECT ROW_NUMBER() over(order by addressiD) as myRow
from [AdventureWorks2012].[Person].[Address])
select myRow as numYear
from mynums
where myRow>2003 and myRow<year(getdate())
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
One (of many) possible Oracle ways:
select to_char(add_months(sysdate, -12 * (level - 1)), 'yyyy')
  from dual
connect by level <= 11;

Open in new window

0
 
LVL 11

Expert Comment

by:Guru Ji
Comment Utility
Select DateFiled
  FROM YourTable
   Where YEAR(DateFiled)> 2003 and YEAR(DateFiled) !=YEAR(GETDATE())
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
You didn't post your table structure so we don't know which fields we need to work out. So will leave here a global example and then you need to change it for your own solution:
SELEC *
FROM YourTableNameHere
WHERE YEAR(DateColumn) BETWEEN 2003 AND YEAR(GETDATE())

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Alexander, this question is in SQL Server area so your code won't work at all.
0
 
LVL 11

Expert Comment

by:Guru Ji
Comment Utility
Sorry

Select DateFiled
  FROM YourTable
   Where YEAR(DateFiled)> 2003 and YEAR(DateFiled) <=YEAR(GETDATE())
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
@Vitor: I know, but the asker has also tagged the question with "Oracle" ;-)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 21

Expert Comment

by:Randy Poole
Comment Utility
Select * from YourTable  Where YEAR(yourdate)> 2003 and YEAR(yourdate)<=YEAR(GETDATE())

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Please note the last sentence: "Note:  all the above years are not stored in any table of SQL Server."

Therefore, a simple Select statement will not work, as the numbers are not stored in any table.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Oh yes. Saw it now. Sorry Alexander.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
@Phillip, I think that only means the years as numbers aren't stored in any table. And how's that your solution returns years if you aren't working with dates?
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
Comment Utility
There's no way I know of to do this in a manner not hard-coded, but the below SQL Server Stored Procedure (I can't speak to Oracle) will return a list of years from 2003 to whatever the current year is, without the dependancy on any pre-existing table:

CREATE PROC year_list AS

CREATE TABLE #years (year_number int) 

Declare @yr int = 2003

WHILE @yr <= YEAR(GETDATE())
   begin
   INSERT INTO #years (year_number) VALUES(@yr) 
   SET @yr = @yr + 1
   end

-- RETURN SET
SELECT year_number FROM #years
GO

Open in new window

0
 
LVL 21

Author Closing Comment

by:Tapan Pattanaik
Comment Utility
Thanks to all experts and special thanks to  Jim Horn.

Note: Here there is no table storing the Years value.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 41
encyps queries mssql 15 26
Why don't I see this table in EDMX file? 2 19
Report Builder 9 25
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now