Solved

SQL question

Posted on 2002-07-14
27
168 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
  • 11
  • 9
  • 6
  • +1
27 Comments
 
LVL 1

Expert Comment

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

Expert Comment

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

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
 
LVL 45

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 45

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 45

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 45

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 45

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 45

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 45

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 45

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 45

Accepted Solution

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

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now