Solved

# SQL question

Posted on 2002-07-14
172 Views
I have a problem that I am not able to solve yet:

Table1                                     Table2

Number   (One to many)                     Number
DateTime in                                DateTime in
DateTime out                               DateTime out
...                                        ...
...                                        ...

How can I get the numbers from table1 where the table1 “DateTime in” to “DateTime out” is not in  Table2 “DateTime in” to “DateTime out” Table2?

(Table1 is a record of a rentcar.
Table2 are records of planned car-rentals.
I have to look of a car is free between a certain period.)

In other words: I have to look if a car can be planned to be rented and therefore I have to look in the table2 where are the records of the planned rentals.
The period between the dates of table1 must not be in table2.

Henk van den Boogaard
0
Question by:Boogaard
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 11
• 9
• 6
• +1

LVL 1

Expert Comment

ID: 7152176
0

LVL 45

Expert Comment

ID: 7152182
Insert Into Table2
(Number , [DateTime in] , [DateTime out])
Select Number , [DateTime in] , [DateTime out]
From Table1
0

LVL 45

Expert Comment

ID: 7152183
Update Table2
Set Table2.[DateTime in] = Table1.[DateTime in] ,
Table2.[DateTime out] = Table1.[DateTime out]
From Table2 inner join Table1 on Table2.[Number] = Table1.[Number]
0

LVL 45

Expert Comment

ID: 7152184
The choice between my two answers depends on your situation.  Do you already have rows in Table2 (Update) or need to append new rows (Insert).
0

LVL 3

Expert Comment

ID: 7152187
Maybe it should be something like that:

SELECT Table1.Number
FROM Table1, Table2
WHERE
(Table1.Number = Table2.Number)
AND(
(Table1.DateIn > Table2.DateOut)
OR
(Table1.DateOut < Table2.DateIn)
)
0

Author Comment

ID: 7152189
(Table1 is a record of a rentcar.
Table2 are records of planned car-rentals.
I have to look of a car is free between a certain period.)

In other words: I have to look if a car can be planned to be rented and therefore I have to look in the table2 where are the records of the planned rentals.
The period between the dates of table1 must not be in table2.
0

LVL 45

Expert Comment

ID: 7152210
Boogaard,

If Table1 is a record of rentals, then it is historical data and not related to future rentals, except for statistical planning purposes.

If you want to know when it is safe to rent a car that hasn't been reserved (planned-to-be-rented), then you need to check for this as follows:
Select *
From Table2
Where
(dtProposedOut Between Table2.DateOut And Table2.DateIn)
Or (dtProposedIn Between Table2.DateOut And Table2.DateIn)
Or (Table2.DateOut Between dtProposedIn And dtProposedOut)
Or (Table2.DateIn Between dtProposedIn And dtProposedOut)

This should return all planned rentals that would interfere with your proposed trip.  In other words, you can only rent those vehicles NOT returned by this query.  To see only those vehicles that CAN be rented, change the Where clause to:
Where
Not(
(dtProposedOut Between Table2.DateOut And Table2.DateIn)
Or (dtProposedIn Between Table2.DateOut And Table2.DateIn)
Or (Table2.DateOut Between dtProposedIn And dtProposedOut)
Or (Table2.DateIn Between dtProposedIn And dtProposedOut)
)

You might want to change the Select clause to:
Select Distinct Number
0

LVL 3

Expert Comment

ID: 7152215
aikimark,

maybe you can make your WHERE clause simplier... like in mine. I think it should work since DateIn < DateOut always, so you can check (if the beginning of the one period is after the end of the other) or (if the end of first is before the beginning of other)
0

LVL 45

Expert Comment

ID: 7152225
LukA_YJK ,

In the rental business, DateOut must come BEFORE DateIn.

My Where clause can not be any simpler and still supply the correct answer to either of the two possible questions:
1. What is already planned-to-be-rented in the proposed period.

2. What can be rented in the proposed period.
0

LVL 3

Expert Comment

ID: 7152254
Sorry, aikimark

I really confused DateIn with DateOut. But I thought that we are looking for a period that did not intersect the given one. If a given period was described as a [DateOut, DateIn] segment, then any segment started after or finished before the given segment is what we are looking for:
DateOut|----------|DateIn
|------------| good
|------| good

So I need to interchange DateIn and DateOut above...
0

Author Comment

ID: 7152278
LukA_YJK and aikimark,

- It is not a classroom assignment, I am to old for that (55) :)

Table1 are rentalcars records and when I have fill in the
planned out and in date and time, I must look for cars that are free that period in table2 where are the (planned) rentals and the assign a free car to that record and add a record in table2, so that the care in planned.

I have tried the solution of aikimark (the one with the NOT)
I have several records in table1 (several cars)
I have one record in table2
When I run the SQL I get:

1. when the date/time in table2 is NOT in the range of the proposed date/time : all the cars in table1.

2. when the date/time in table2 is IN the range of the proposed date/time : none of the cars in table1.

greetings,
Henk

0

LVL 45

Expert Comment

ID: 7152287
LukA_YJK,

Your second example is mislabeled when displayed with proportional fonts:

________DateOut|========|DateIn
____|=====| good
_______________________________|========| good

Your SQL Where clause needs to accomodate overlap in both directions.

I might be able to simplyfy this similar to your query:
Where
(dtProposedIn <= Table2.DateOut)
And (dtProposedOut >= Table2.DateIn)
0

LVL 3

Expert Comment

ID: 7152304
Mr.Henk

As far as I understood there may be few cars free for rent in the given period. So as aikimark proposed it is better to find out all free cars first. Maybe my version will work:

SELECT Number
FROM Table2
WHERE
(dtProposedIn < Table2.DateOut)
OR
(dtProposedOut > Table2.DateIn)

then you can select one of these cars and simply add it to Table1. Something like that:

with Table1 do begin
Append;
FieldByName('Number').Value := Query1.FieldByName('Number').Value;
FieldByName('DateOut') := dtProposedOut;
FieldByName('DateIn') := dtProposedIn;
Post;
end;
0

LVL 3

Expert Comment

ID: 7152309
Mr.Henk

As far as I understood there may be few cars free for rent in the given period. So as aikimark proposed it is better to find out all free cars first. Maybe my version will work:

SELECT Number
FROM Table2
WHERE
(dtProposedIn < Table2.DateOut)
OR
(dtProposedOut > Table2.DateIn)

then you can select one of these cars and simply add it to Table1. Something like that:

with Table1 do begin
Append;
FieldByName('Number').Value := Query1.FieldByName('Number').Value;
FieldByName('DateOut') := dtProposedOut;
FieldByName('DateIn') := dtProposedIn;
Post;
end;
0

LVL 3

Expert Comment

ID: 7152315
Sorry, aikimark

You wrote
(dtProposedIn <= Table2.DateOut)
And
(dtProposedOut >= Table2.DateIn)

But as you said DateOut<DateIn always
then dtProposedIn<DateOut < DateIn<dtProposedOut
so dtProposedIn<dtProposedOut
and this will be always FALSE... no records...

Also sorry for double posting...
0

Author Comment

ID: 7152322
LukA_YJK,

In your example: if there are no records in table2 for a car (a new car?) then i get no record for that car.
So I think I must take as foundation table1 (the car) and
then look in table2 to look if it is free (and also all the cars that are free also) so I can make a choice.

Thanks for the hard thinking,
Henk
0

LVL 45

Expert Comment

ID: 7152332
LukA_YJK,

You are correct.  Good catch.  My Where clause was mistyped.  It should have an "OR" boolean connector instead of the "AND" that I used.
Corrected Example:
Where
(dtProposedIn <= Table2.DateOut)
Or (dtProposedOut >= Table2.DateIn)

My simplified query finds proposed rentals:

1. whose DateOut happens after the proposed return of the car (dtProposedIn)

Or

2. whose DateIn happens before the proposed rental of the car (dtProposedOut)
0

LVL 45

Expert Comment

ID: 7152335
Boogaard ,

Is this a real database or a classroom example?

You should have a Car table that can be left-joined with Table2 to show all cars, regardless of their planned-to-be-rental status.
0

LVL 3

Expert Comment

ID: 7152339
Mr.Henk

Now I understood (hope correctly) the problem completely. So the Table1 is the table of all cars, but Table2 is the table of rented cars and their rent periods...
So as aikimark proposed we must find out all cars (numbers) that are free during the given period (is not a car which is rented during this period)
So the SQL may be as follows:

SELECT *
FROM Table1
WHERE Number NOT IN
(
SELECT Number
FROM Table2
WHERE
(dtProposedIn < Table2.DateOut)
OR
(dtProposedOut > Table2.DateIn)
)

Then you can select one of these cars and simply add it to Table2 as I wrote above (changing Table1 to Table2 of course)
0

LVL 3

Expert Comment

ID: 7152342
Sorry, I should write
WHERE NOT Number IN
0

LVL 45

Expert Comment

ID: 7152348
that doesn't cut it either.  There may be cars that have never been rented and aren't planned to be be rented.
0

LVL 3

Expert Comment

ID: 7152390
Sorry, it should be WHERE Number NOT IN as before...
I think it should work: if there is no cars in Table2 the SELECT of IN clause will return an empty set, so NOT IN should return all Numbers in Table1, that is all cars.
We can try it, at least, too...
0

LVL 45

Expert Comment

ID: 7152443
Table1 contains cars that have been rented.
0

Author Comment

ID: 7152510
Sorry Both of you, but all the solutions are not working in my DB. (is not a school example but a live Flashfiler 2.10 DB)
The problem is that the colom-names are dutch and there are 3 tables involved 1. cars, rental-records (a smal record with dates, carlicence e.d.), rental order (all the information like drivinglicence, name driver e.d. (I had only mentioned 2 to simplify the case)

I be away for one hour now and when I am back a will make a test DB with table1 and table2 (with englisch colomnames) to test.
Then I will make a test with both the solutions and exsperiment with the dates.

Henk
0

LVL 45

Accepted Solution

aikimark earned 100 total points
ID: 7152548
Select *
From Cars Left Join [rental order]
On Cars.Number = [rental order]. Number
Where
(
(dtProposedIn <= [rental order].DateOut)
OR
(dtProposedOut >= [rental order].DateIn)
)

OR [rental order].Number Is Null
0

Author Comment

ID: 7152660
aikimark,

You did the trick. I only added DISTINCT to avoid doubble
records from a car when there are more than one records in the [rental order] db.

Thanks, and also thanks to LukA_YJK.

Henk van den Boogaard
0

Author Comment

ID: 7152663
Thanks again for the excellent job!!!
Henk
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
###### Suggested Courses
Course of the Month2 days, 17 hours left to enroll