Help with tricky T-SQL

Johnny Ljunggren

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

Feb 7 '06 #1

Erland Sommarskog

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

Feb 7 '06 #2

ZeldorBlat

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.

Feb 8 '06 #3

onkelringnes

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

Feb 8 '06 #4

--CELKO--

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'.

Feb 9 '06 #5

Tony Rogerson

> 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'.

Feb 10 '06 #6