sql server programming: I posted this in the DataAccess form by mistake (sorry for the double post).

I'm having a problem trying to figure out how to create a query for the following problem. Any help would be greatly appreciated.

The following two tables define events occuring during a satellite revolution.
REV
revid INT IDENTIT (PK)
revnum INT

EVENT
eventid INT IDENTITY (PK)
revid INT (FK)
eventDateTime Datetime
eventName varchar(20)

So here is some example data. The number of events per rev is variable but the example data shows 3 events per rev for simplicity.

REV
revid revnum
1 100
2 101
3 102

EVENT
eventid revid eventDateTime eventName
1 1 2000-01-01 01:00:00 rise
2 1 2000-01-01 05:10:00 apogee
3 1 2000-01-01 09:20:00 set
4 2 2000-01-01 13:30:00 rise
5 2 2000-01-01 17:40:00 apogee
6 2 2000-01-01 22:00:00 set
7 3 2000-01-02 02:10:00 rise
8 3 2000-01-02 06:20:00 apogee
9 3 2000-01-02 10:30:00 set

So now I need to figure out which rev is the current rev based on the current server time. So lets say that the current date/time is 2000-01-01 15:00:00 which is in revnum 101 (revid=2).

Question 1: How do I write a query that returns the revnum for the current time?

Question 2: If the current time occurs between revs (the time between set on one rev and rise on the next) how do I return the upcomming revnum?

Thanks in advance from a novice SQL student.

Chuck

This query gets the results for both questions:
SELECT TOP 1 r.revnum
FROM rev r
INNER JOIN event e
ON r.revid = e.revid
WHERE e.eventdatetime >= GETDATE()
ORDER BY e.eventdatetime


--
Jacco Schalkwijk
SQL Server MVP


[quoted text, click to view]
current server time. So lets say that the current date/time is 2000-01-01
15:00:00 which is in revnum 101 (revid=2).
[quoted text, click to view]