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 2313
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: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: tracyyun |
last post by:
Hello everyone,
I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: Teri B |
last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: nia12 |
last post by:
Hi there,
I am very new to Access so apologies if any of this is obvious/not clear.
I am creating a data collection tool for health care employees to complete. It consists of a number of...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
|
by: isladogs |
last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...
|
by: GKJR |
last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
| |