Posted by --CELKO-- on 10/01/59 11:39

Please post DDL for people. I cleaned up your pseudo-code and added
constraints.

CREATE TABLE Events
(event_id INTEGER NOT NULL PRIMARY KEY,
vid INTEGER NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
CHECK (start_time < end_time));

INSERT INTO Events VALUES (3, 2, '2006-02-09 10:00', '2006-02-09
14:00');
INSERT INTO Events VALUES (4, 2, '2006-02-09 16:00', '2006-02-09
17:00');
INSERT INTO Events VALUES (5, 2, '2006-02-09 18:00', '2006-02-09
19:00');
INSERT INTO Events VALUES (6, 2, '2006-02-09 20:00', '2006-02-09
21:00');
INSERT INTO Events VALUES (7, 3, '2006-02-09 11:00', '2006-02-09
13:00');
INSERT INTO Events VALUES (8, 3, '2006-02-09 15:00', '2006-02-09
16:00');
INSERT INTO Events VALUES (9, 3, '2006-02-09 18:00', '2006-02-09
20:00');

It looks like you are trying to find the start of the next event, like
waiting for a train.

BEGIN
DECLARE @my_time DATETIME;
SET @my_time = '2006-02-09 16:30:00';

SELECT E1.event_id, E1.vid, E1.start_time, E1.end_time
FROM Events AS E1
WHERE start_time
= (SELECT MIN(start_time)
FROM Events AS E1
WHERE @my_time < E1.start_time);
END;

This means that you will get two rows for 16:30 Hrs, namely 5 and 9,
which both start at '2006-02-09 18:00:00.000'.

[Back to original message]