Sargable Previous Month 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)

Sargable Previous Month Query

Author

Topic

AndyB
Starting Member

5 Posts

Posted - 2003-11-01 : 13:42:22

Hey,

I'm tyring to write a sargable query that shows the data from the previous month.

So far I have this.. where EndTime is a datetime column. There is a non-clustered index on EndTime. If I switch the DateAdd(..) with a hardcoded date, it of course uses the index.

I'm using SQL 7

SELECT *
FROM mytable
WHERE
EndTime BETWEEN DATEADD(mm, DATEDIFF(mm,0,getdate()) -1, 0)
AND DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

AndyB
Starting Member

5 Posts

Posted - 2003-11-01 : 13:46:21

Forgot to mention, This must be a single select statement that can be made into a view....

AndyB
Starting Member

5 Posts

Posted - 2003-11-01 : 14:10:35

quote:
Originally posted by ehorn

Perhaps this will use the index..

WHERE EndTime >= DATEADD(mm, DATEDIFF(mm,0,getdate()) -1, 0)
AND EndTime < DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

Related Reading - http://www.sql-server-performance.com/transact_sql.asp

Didn't work...

For what it's worth, there is 5 years data in here, so the query should be returning about 1/60th of it... so the optimizer shouldn't go to a table scan because it's returning too much data compared to the whole.

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-01 : 15:25:20

Shouldn't need to do this but

DECLARE @StartDate DATETIME
DECLARE @FinishDate DATETIME

SET @StartDate = DATEADD(mm, DATEDIFF(mm,0,getdate()) -1, 0)

SET @FinishDate = DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

SELECT *
FROM mytable
WHERE
EndTime BETWEEN @StartDate AND @FinishDate

If that doesn't work, you may need to consult the guru's of INDEXing.

or, you might experiment with HINTs.

[url]http://sqlteam.com/Forums/topic.asp?TOPIC_ID=11313&SearchTerms=index,hint[/url]

AndyB
Starting Member

5 Posts

Posted - 2003-11-01 : 18:12:10

Thanks that works, but I REALLY need it in a single select statement that can be made into a view.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-11-02 : 15:01:39

quote:
For what it's worth, there is 5 years data in here, so the query should be returning about 1/60th of it...

But if you look at the estimate, it will be expecting 9% of the rows, since it's only able to guess with those WHERE conditions, and it guesses 30% for each (0.3*0.3 = 0.09).
So you'll either have to give it an index hint or fool it with redundant conditions (ones that work on the values in index only!)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-02 : 18:04:05

Are you sure that the column is Indexed?

If I add an index to the Ord_Date in the Sales table in PUBS, I get a INDEX SEEK with a LOOKUP.


USE PUBS
GO
create index IDX_Date ON Sales(ord_Date)
GO
Select * from Sales
WHERE
ord_date BETWEEN DATEADD(mm, DATEDIFF(mm,0,getdate()) -1, 0)
AND DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
GO

DavidM

"SQL-3 is an abomination.."

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-02 : 18:34:22

I believe this will force the index using a lookup table (dates)


create table dates(n int identity(0,1), dmin datetime, dmax datetime primary key)
go
insert into dates select '8/1/2003','9/1/2003'
insert into dates select '9/1/2003','10/1/2003'
insert into dates select '10/1/2003','11/1/2003'
insert into dates select '11/1/2003','12/1/2003'
insert into dates select '12/1/2003','1/1/2004'
insert into dates select '1/1/2004','2/1/2004'
insert into dates select '2/1/2004','3/1/2004'
insert into dates select '3/1/2004','4/1/2004'
go

select q.* 
from mytable q join dates d on q.EndTime < d.dmax
where q.endtime between d.dmin and d.dmax
and d.dmax = (select top 1 dmax from dates where dmax <= getdate() order by dmax desc)
order by endtime

AndyB
Starting Member

5 Posts

Posted - 2003-11-03 : 06:57:08

Thanks for the advice, I'll look up my facts and try a few things and get back with much later today. I'll post the query excution plans for the various methods so you can see how each one worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources