Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL question

Posted on 2002-07-14
27
Medium Priority
?
177 Views
Last Modified: 2010-04-04
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.

Can you please help me?
Thanks in advance,
Henk van den Boogaard
0
Comment
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
  • Learn & ask questions
  • 11
  • 9
  • 6
  • +1
27 Comments
 
LVL 1

Expert Comment

by:Gani2001
ID: 7152176
More information...
0
 
LVL 46

Expert Comment

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

Expert Comment

by:aikimark
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Expert Comment

by:aikimark
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

by:LukA_YJK
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

by:Boogaard
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 46

Expert Comment

by:aikimark
ID: 7152210
Boogaard,

Your question doesn't make sense.  If this is a classroom assignment, please ask your teacher to clarify this.

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

by:LukA_YJK
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 46

Expert Comment

by:aikimark
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

by:LukA_YJK
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
    |----------------| bad
                             |------------| good
|------| good

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

Author Comment

by:Boogaard
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 46

Expert Comment

by:aikimark
ID: 7152287
LukA_YJK,

Your second example is mislabeled when displayed with proportional fonts:

________DateOut|========|DateIn
______|==========| bad
____________________|=========| bad
____|=====| good
_______________________________|========| good
_____|============================| bad


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

by:LukA_YJK
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

by:LukA_YJK
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

by:LukA_YJK
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

by:Boogaard
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.

Please see also my comment, with your example I don't get cars either way.

Thanks for the hard thinking,
Henk
0
 
LVL 46

Expert Comment

by:aikimark
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 46

Expert Comment

by:aikimark
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

by:LukA_YJK
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

by:LukA_YJK
ID: 7152342
Sorry, I should write
WHERE NOT Number IN
0
 
LVL 46

Expert Comment

by:aikimark
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

by:LukA_YJK
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 46

Expert Comment

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

Author Comment

by:Boogaard
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 46

Accepted Solution

by:
aikimark earned 400 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

by:Boogaard
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

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question