Hello all
I've got this tricky situation that I would like to solve in SQL, but
don't know how to do. This is the table:
Id = 3, VId = 2, Time1 = 10:00, Time2 = 14:00
Id = 4, VId = 2, Time1 = 16:00, Time2 = 17:00
Id = 5, VId = 2, Time1 = 18:00, Time2 = 19:00
Id = 6, VId = 2, Time1 = 20:00, Time2 = 21:00
Id = 7, VId = 3, Time1 = 11:00, Time2 = 13:00
Id = 8, VId = 3, Time1 = 15:00, Time2 = 16:00
Id = 9, VId = 3, Time1 = 18:00, Time2 = 20:00
GetRows @Time='15:30' will return row with Id=4
GetRows @Time='16:30' will return row with Id=4 and row=9
Logic behind this:
Return row n where Time2 of Id=(n-1) < @Time < Time 1 of Id=(n) and same
VId.
Ie. if @Time = '15:30' then Time2 of Id = 3 is lower than @Time, and
Time1 of Id = 4 is higher than @Time => return row with Id = 4.
This got a bit messy but if someone could decipher this and possibly
give an answer I'd be very glad.
regards
Johnny
Johnny Ljunggren (johnny@navtek.no) writes: [quoted text, click to view] > I've got this tricky situation that I would like to solve in SQL, but
> don't know how to do. This is the table:
>
> Id = 3, VId = 2, Time1 = 10:00, Time2 = 14:00
> Id = 4, VId = 2, Time1 = 16:00, Time2 = 17:00
> Id = 5, VId = 2, Time1 = 18:00, Time2 = 19:00
> Id = 6, VId = 2, Time1 = 20:00, Time2 = 21:00
> Id = 7, VId = 3, Time1 = 11:00, Time2 = 13:00
> Id = 8, VId = 3, Time1 = 15:00, Time2 = 16:00
> Id = 9, VId = 3, Time1 = 18:00, Time2 = 20:00
>
> GetRows @Time='15:30' will return row with Id=4
> GetRows @Time='16:30' will return row with Id=4 and row=9
>
> Logic behind this:
> Return row n where Time2 of Id=(n-1) < @Time < Time 1 of Id=(n) and same
> VId.
>
> Ie. if @Time = '15:30' then Time2 of Id = 3 is lower than @Time, and
> Time1 of Id = 4 is higher than @Time => return row with Id = 4.
SELECT a.ID, a.VId, a.Time1, a.Time2 FROM tbl a JOIN tbl b ON a.VId = b.VId AND a.ID = b.ID +1 WHERE a.Time1 > @Time AND b.Time2 < @Time Here I've taken your description by the letter. I strongly suspect that in real life the ids are not contiguous. But since I don't know what the real plot is, I did not want to do guessworks. The above query is not tested, as you did not include CREATE TABLE and INSERT statements. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
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'.
[quoted text, click to view] > 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... [quoted text, click to view] > SET @my_time = '2006-02-09 16:30:00';
Use the correct ISO formatting - 2006-02-09T16:30:00 [quoted text, click to view] > 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 [quoted text, click to view] "--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'.
>
Don't see what you're looking for? Try a search.
|
