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 5 2333
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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Tim |
last post by:
Hello,
I'm extremely puzzled; I cannot figure out what I'm doing wrong. Here's
the situation. I would really appreciate any suggestions.
I'm modifying a shopping cart in the following way....
|
by: lorirobn |
last post by:
Hello,
I'd be curious to hear other thoughts on my database and table design.
My database is for 'space use' in a lodging facility - will hold all
spaces, like rooms (lodging rooms, dining...
|
by: Fahad |
last post by:
I am a newcommer in c world,
I am making a tiny program on Mathematics.
I have one problem if somebody enter his name, "i am able to make the
first letter uppercase but my problem how can i make...
|
by: SK |
last post by:
Hi
I am trying to teach myself how to program in C. I am a physician
hoping to be able to help restructure my office. Anyhow, I amhoping
that the porblem I am having is simple to those much more...
|
by: Bharat |
last post by:
Hi Folks,
Suppose I have two link button on a page (say lnkBtn1 and
lnkBtn2). On the click event of the lnkbtn1 I have to add a dynamically
created control. And On the click event of the lnkBtn2 I...
| |
by: Jeff C |
last post by:
Hello all. Please please help me come up with a solution to this
problem.
Here it goes-
--- code snipit---
'TabControl1
'
Me.TabControl1.Anchor = System.Windows.Forms.AnchorStyles.Left...
|
by: MorrganMail |
last post by:
Or at least I find it tricky. :-)
Assume we have three tables A, B and C. Table A contains a path and the
distance for traveling that path:
A (PathId, NodeId, Dist (from previous node))
1, 1,...
|
by: fleece |
last post by:
Hi, I need help on this tricky access permission issue:
For my database, I want to allow users be able to edit his/her records, insert new ones and review other user's records but no edit. How to...
|
by: ebernedo |
last post by:
Hey guys, I'm new here
and also pretty new to access
I'm creating a database at work, and need to be able to search things within it to find the right part number in our case...
heres a sample...
|
by: Kasterborus |
last post by:
I'm having trouble writing a loop using integer math that will
spit out 10 numbers and slowly make them all tend to 2048.
Ideally this loop should take about 400 iterations, and with each pass...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |