Posted by Erland Sommarskog on 02/08/06 00:37

Johnny Ljunggren (johnny@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, 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
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]