Need help for SQL query

Question

Post reply

Need help for SQL query

gurukiran.bhat

SSC Veteran

Points: 262
More actions
September 4, 2009 at 4:10 am

#209586

Hi,
I need to write a SQL query which has to allow the user to enter the year during runtime. Can any one please help me to do this.
Guru.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

SELECT YourDate

FROM YourTable

WHERE (DATEPART(yyyy, YourDate) = @Enter_Year)

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

T-SQL does not prompt a user automatically, the way that Access does for example. Prompting for user input is a function of the user interface, including Access or SSMS. If you write a stored procedure with input parameters and right click on it in the object explorer of SSMS and choose "execute stored procedure" you will be prompted for all input parameters.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 3

if you have an index on this column with date type a query like this:

select * from sys.databases

where DATEPART(year,create_date) = 2003

will be very slow (index scan is required here), you should use something like this:

select * from sys.databases

where create_date >= '20030101' and create_date < '20040101'

Paul White SSC Guru Points: 150467 More actions · Answer 4

Marcin Gol [SQL Server MVP] (9/6/2009)
select * from sys.databases
where DATEPART(year,create_date) = 2003
will be very slow (index scan is required here)

There is an interesting case where this isn't true (though it only applies to user tables, not system ones):

SET NUMERIC_ROUNDABORT OFF;

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;

GO

CREATE TABLE dbo.DateIndexTest

(

row_id INTEGER IDENTITY PRIMARY KEY,

any_date DATETIME NOT NULL,

any_date_year AS YEAR(any_date),

);

GO

INSERT dbo.DateIndexTest WITH (TABLOCKX)

(any_date)

SELECT TOP (1000)

DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), CURRENT_TIMESTAMP)

FROM master.sys.all_columns C1, master.sys.all_columns C2, master.sys.all_columns C3;

GO

-- Indexes

CREATE NONCLUSTERED INDEX nc1 ON dbo.DateIndexTest(any_date ASC);

CREATE NONCLUSTERED INDEX nc2 ON dbo.DateIndexTest(any_date_year ASC);

GO

-- *** Index seek! ***

SELECT COUNT_BIG(*)

FROM dbo.DateIndexTest

WHERE DATEPART(YEAR, any_date) = 2010;

GO

DROP TABLE dbo.DateIndexTest;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 5

Paul ... but here you have a "special" index on field: any_date_year AS YEAR(any_date) so 😉

but sql can handle with this optimization in very elegant way - it is quite similar to hierarchyid case.

Nabha SSCrazy Eights Points: 8534 More actions · Answer 6

Dear Paul,

Can you please help me understand how this is happening? :hehe:

SELECT count(*)

FROM dbo.DateIndexTest -- returns 1000 records

SELECT count(*)

FROM dbo.DateIndexTest

WHERE any_date_year = 2010 -- 365 records

SELECT count(*)

FROM dbo.DateIndexTest

WHERE DATEPART(YEAR, any_date) = 2010; -- 365 records

---------------------------------------------------------------------------------

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

PP:

I see two queries against different columns. What does that prove?

Also, do you have an index built over either date column? That was Paul's point.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Nabha SSCrazy Eights Points: 8534 More actions · Answer 8

Thanks Bob for the reply,

I just ran the script given by Paul completely, I was just trying to understand how thats happening.Thats it.

I mean, there were 114 records with 2009 as year and rest all 2010, but when we execute that query, its returning only 365 records!

I know this much that if you use any function on a column having index, the search will go for a table (heap) scan and not an index scan..

Here not able to understand whats happening.

---------------------------------------------------------------------------------

Paul White SSC Guru Points: 150467 More actions · Answer 9

PP (9/7/2009)
I just ran the script given by Paul completely, I was just trying to understand how thats happening.Thats it.
I mean, there were 114 records with 2009 as year and rest all 2010, but when we execute that query, its returning only 365 records!
I know this much that if you use any function on a column having index, the search will go for a table (heap) scan and not an index scan..
Here not able to understand whats happening.

The happen to be only 365 days in 2010 :w00t:

Try:

SELECT any_date_year, COUNT_BIG(*) AS row_count

FROM dbo.DateIndexTest

GROUP BY

any_date_year

ORDER BY

any_date_year;

You'll see that there are 114 rows (1 per day) in 2009, 365 in 2010, 365 in 2011, and 156 in 2012 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 10

Marcin Gol [SQL Server MVP] (9/7/2009)
Paul ... but here you have a "special" index on field: any_date_year AS YEAR(any_date) so 😉

Yes - I cheated slightly to illustrate a point: a function on a column doesn't *always* force an index scan (not if you have the right index :laugh:)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 11

Okay, I'm in synch with your question now. Sorry for the confusion. It seems to be my day for it.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Paul White SSC Guru Points: 150467 More actions · Answer 12

Bob Hovious (9/7/2009)
Okay, I'm in synch with your question now. Sorry for the confusion. It seems to be my day for it.

I wouldn't worry about it Bob - I had to read it several times too. I think I answered the right question.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 13

I think I will start referring to you as Emperor Paulpatine.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Paul White SSC Guru Points: 150467 More actions · Answer 14

Bob Hovious (9/7/2009)
I think I will start referring to you as Emperor Paulpatine.

Truly, you do deserve your reputation as a monitor-killer.

I was enjoying that cup of coffee too 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi