Returning next successive value in a list

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

SQL Server Development (2000)

Returning next successive value in a list

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-05 : 17:37:33

Okay, here's what should be an easy question (but of course, yours truly is having problems). I've got a bunch of Dates in a table:

Date
Jan-1
Jan-2
Jan-5
Jan-11
Jan-19
Jan-20

However, let us assume that the difference between each date period is not uniform (i.e. it's not one day so that you get jan 1, 2004, jan 2, 2004, and so on but irregular so I've got stuff like jan 1, 2004, jan 5 2004, jan 6 2004, jan 18, 2004, etc).

For any given date, how would I return the very next record if I don't know the interval in advance (because it would be easy to say: select Date+1 or something like that).

I've got a feeling I need to use a subquery which specifies something along the lines of:

select min(Date) from....where Date > currentdate [or something].

Thanks in advance.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-05-05 : 20:03:03

you were close

select a.date as firstdate, min(b.date) as nextdate 
from dates a, dates b
where b.date > a.date
group by a.date

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Ex
Posting Yak Master

166 Posts

Posted - 2005-05-05 : 20:04:29

strike my answer rrb got there first :)

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

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Rich Cook

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-06 : 09:04:07

Thank you very much. I appreciate it!

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-05-08 : 19:40:20

Hey EX

Like your quote - here's one for you!
-----
I'm not schooled in the science of human factors, but I suspect surprise is not an element of a robust user interface.
--Chip Rosenthal

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Ex
Posting Yak Master

166 Posts

Posted - 2005-05-09 : 01:43:46

i like it :) to bad it so easy to surprise a user move a windows into a new corner and 'some' users are lost :)

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

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Rich Cook

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources