Adding where clause to query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Adding where clause to query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-06-24 : 00:21:47

I have a procedure as follows below. I need to modify it so that it only selects the records for a certain month.

What would be the best way to do this? I am thinking of passing the current date to it, to find out statistics for the current month, or passing the current date minus a month to find the previous months statistics.

If this is a decent way of doing this, how can I accomplish that in the SQL code.

Thanks alot

Mike123


CREATE procedure select_referers_month
(
@date datetime
)	
AS SET NOCOUNT ON

SELECT nameonline, COUNT(tblReferalLog.userid) AS ReferredCount 

FROM TBLUSERDETAILS 

INNER JOIN tblReferalLog ON TBLUSERDETAILS.userid = tblReferalLog.userID

GROUP BY nameonline, tblReferalLog.userID 

ORDER BY referredCount desc


GO

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-24 : 03:36:46

CREATE procedure select_referers_month
(
@date datetime
)
AS SET NOCOUNT ON

SELECT nameonline, COUNT(tblReferalLog.userid) AS ReferredCount

FROM TBLUSERDETAILS

INNER JOIN tblReferalLog ON TBLUSERDETAILS.userid = tblReferalLog.userID

WHERE DATEPART(mm, tblReferalLog.ReferDate) = DATEPART(mm, @Date)

AND DATEPART(yy, tblReferalLog.ReferDate) = DATEPART(yy, @Date)

GROUP BY nameonline, tblReferalLog.userID

ORDER BY referredCount desc

GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-24 : 11:29:31

WHERE DATEPART(mm, tblReferalLog.ReferDate) = DATEPART(mm, @Date)
AND DATEPART(yy, tblReferalLog.ReferDate) = DATEPART(yy, @Date)

that cannot make use of indexes. Try to avoid WHERE clauses that have formulas on both sides of the operators.

Try something like:

WHERE ReferDate BETWEEN dateadd(mm,datediff(mm,0,@date),0) AND dateadd(mm,datediff(mm,0,@date)+1,0)

That should be much more efficient, especially if there is an index on ReferDate.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-24 : 17:04:34

Ahhh....Stage 2 Non Sargable Predicates...

http://www.quest.com/whitepapers/10_SQL_Tips.pdf

Brett

8-)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-06-24 : 19:26:13

thanks alot guys, worked great!

cheers

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources