SQL question

More information...

Insert Into Table2
(Number , [DateTime in] , [DateTime out])
Select Number , [DateTime in] , [DateTime out]
From Table1

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]

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).

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)
)

(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.

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

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)

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.

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...

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

 

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)

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;

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;

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...

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

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)

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.

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)

Sorry, I should write
WHERE NOT Number IN

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

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...

Table1 contains cars that have been rented.

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

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

Thanks again for the excellent job!!!
Henk