|
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 | |
Share:
|
Johnny Ljunggren (jo****@navtek.no) writes: 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, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx | | |
Erland Sommarskog wrote: 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, es****@sommarskog.se
I run into this all the time. You're right, Erland, that it's easy
when the keys are contiguous, but all too often they aren't. Finding
the "next" or "previous" row within a group is not something that SQL
is very good at. I found myself writing a lot of this crap:
SELECT a.ID, x.ID prevID
FROM tbl a
JOIN (SELECT a1.ID, max(a2.ID) prevID
FROM tbl a1
JOIN tbl a2 ON (a1.id > a2.id
AND a1.col1 = a2.col1
AND a1.col2 = a2.col2
AND etc....)
GROUP BY a1.ID) x ON a.ID = x.ID
All that just to find the "previous" row within the group. Needless to
say I've since moved away from this approach in favor of doing the hard
work in some other programming language. | | |
Erland Sommarskog skrev: Logic behind this: Return row n where Time2 of Id=(n-1) < @Time < Time 1 of Id=(n) and same VId.
<snip example>
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.
Thanks for the effort. You're very right that the ids are not
contiguous so I may be better off do it in my application as ZeldorBlat
pointed out.
Anyway, I learned something new which is always a good thing :)
Johnny | | |
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'. | | |
> 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--" <jc*******@earthlink.net> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.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'. | | This discussion thread is closed Replies have been disabled for this discussion. Similar topics
1 post
views
Thread by Tim |
last post: by
|
4 posts
views
Thread by lorirobn |
last post: by
|
4 posts
views
Thread by Fahad |
last post: by
|
27 posts
views
Thread by SK |
last post: by
|
10 posts
views
Thread by Bharat |
last post: by
|
2 posts
views
Thread by Jeff C |
last post: by
|
1 post
views
Thread by MorrganMail |
last post: by
| | |
5 posts
views
Thread by Kasterborus |
last post: by
| | | | | | | | | | |