get value on fixed date: Part 2

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: Part 2

Author

Topic

Vadim Scherbakov
Starting Member

5 Posts

Posted - 2002-02-10 : 08:12:42

I glad to see that many people will interesting my problem, and I want approach to real task.

There are table with following structure:
objectid position value
--------- ---------------- -------
1 20010301ACSFSDSA 1
2 20010501SDASDDDA 6
1 20010701ASDAAADS 3
2 20010401ADASDZDS 5

Where position is unique (date, time and position within second)

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

_Stored_Procedure(@CurDate AS Datetime)

For example:

_Stored_Procedure('06.01.01') must return

object value
------ -------
1 1
2 6

I want to receive answer, that make this operation by 1 pass, without joins and etc, because number of objects about 10000

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-10 : 08:19:06

Nigel's solution from the first post will work if you remove the TOP 1. This is a modification of it:

CREATE PROCEDURE GetDateValue @date datetime
SELECT objectid, value
FROM tbl WHERE position <= @date

Vadim Scherbakov
Starting Member

5 Posts

Posted - 2002-02-10 : 08:27:00

To robvolk:

Your code will return all values with position less than @CurDate.
I need to get a ONE values for each object on the MAX date, but less than @CurDate

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-10 : 08:35:06

Then you must use a subquery:

CREATE PROCEDURE GetDateValue @date datetime
SELECT A.objectid, A.value
FROM tbl A INNER JOIN
(SELECT objectid, Max(position) MaxDate FROM tbl WHERE position <= @date GROUP BY objectid) B
ON (A.objectid=b.objectid AND A.position=B.MaxDate)

And don't ask me to do it without a subquery or join. I read your original post, and this is the best solution I can think of that provides the results you want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources