get value on fixed date - 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)

get value on fixed date

Author

Topic

Vadim Scherbakov
Starting Member

5 Posts

Posted - 2002-02-09 : 13:02:04

There are table with following structure:
date value
--------- -------
01.01.01 1
05.01.01 6
07.01.01 3

It is need to create stored procedure, that determine value on specified date.

_Stored_Procedure(@CurDate AS Datetime)

For example:

_Stored_Procedure('03.01.01') must return 1, but _Stored_Procedure('05.01.01') must return 6

I want to receive answer, that make this operation by 1 pass, without joins and etc.

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-09 : 13:05:43

CREATE PROCEDURE GetDateSP @curDate datetime AS
SELECT value FROM myTable WHERE dateCol=@curDate

This might not work if the date column also includes time values, but this version will:

CREATE PROCEDURE GetDateSP @curDate datetime AS
SELECT value FROM myTable
WHERE DateDiff(dd, dateCol, @curDate)=0

Edited by - robvolk on 02/09/2002 13:06:11

Vadim Scherbakov
Starting Member

5 Posts

Posted - 2002-02-09 : 13:12:52

quote:

CREATE PROCEDURE GetDateSP @curDate datetime AS
SELECT value FROM myTable WHERE dateCol=@curDate

This might not work if the date column also includes time values, but this version will:

CREATE PROCEDURE GetDateSP @curDate datetime AS
SELECT value FROM myTable
WHERE DateDiff(dd, dateCol, @curDate)=0

Edited by - robvolk on 02/09/2002 13:06:11

carefully read my first example. There may be @CurDate with no according dates in table

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-09 : 13:22:29

select value
from tbl
where date = (select max(date) from tbl where date <= @date)

select top 1 value
from tbl
where date <= @date
order by date desc

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 02/09/2002 13:30:24

Vadim Scherbakov
Starting Member

5 Posts

Posted - 2002-02-09 : 13:31:05

quote:

select value
from tbl
where date = (select max(date) from tbl where date <= @date)

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

I write that I need answer with 1 pass, because size of table vary big

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-09 : 13:33:57

select top 1 value
from tbl
where date <= @date
order by date desc

If the table is indexed on date then this will not be much different from the subquery - may even be slower.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-09 : 14:14:02

Are the date values unique? If they aren't, then you're gonna have problems due to duplicate date values. Nigel's TOP version of the query will only return 1 row if there are dupes, and may not be the row you're looking for.

The subquery would return multiple rows that meet that criteria...again, you may not want this, but you haven't said otherwise, and it will at least return all candidate rows.

If date values ARE unique, and you are not storing time values as well, then the subquery that Nigel offered is the best solution. Even if you had data for every single day for the last 100 years, you're still talking about only 36,500 rows...that's not a "very big table", and with indexes on the date column the subquery will run very nicely.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-09 : 14:19:59

I guess you have other fields in the table to make it big. If so just put a non-clustered index on date (or date, value) and use the subquery version. This should search through the index to find the result and not touch the table at all (just the one page if you don't include value in the index).

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-09 : 14:36:54

With a covering index on (date, value) both queries gets done as an index seek and top (MSSQL 2000; haven't got anything older to test).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources