Posted by Tony Rogerson on 10/01/00 11:39

> INSERT INTO Events VALUES (3, 2, '2006-02-09 10:00', '2006-02-09
> 14:00');

This is very dangerous code, its worse than SELECT * and relies columns
being in order which we know in a set is just not the case.

ALWAYS specify the columns on your INSERT...

> SET @my_time = '2006-02-09 16:30:00';

Use the correct ISO formatting - 2006-02-09T16:30:00

> 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;

You should have listened in class when they taught proper indentation on a
code block...

BEGIN
DECLARE ...
SET ....

END

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1139515459.317448.97060@g47g2000cwa.googlegroups.com...
> 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]