we use a table reserving cars between date a and date b. Now we would check, if the date period from the new reservation is partially in the period from another record for the same car.
User A Car a from 01.01.2021 to 10.01.2021
Now would User B uses the Car a from 09.01.2021 to 11.01.2021.
In this case the User B must get a hint, that the car is already reserved.
Can I realize that with getting a list from the date period from the old entry and check, if the new period in it?
>> Can I realize that with getting a list from the date period from the old entry and check, if the new period in it?
What is your actual problem? Do you ask if you can or ask how to do this because you tried and query do not work?
Answer is yes, you can realise it.
If you ask how, then consider dates:
D1_FROM, D1_TO, D2_FROM, D2_TO
Dates are not overlaped when
D1_FROM>D2_TO OR D1_TO<D2_FROM
Then testing if they are overlaped:
NOT (D1_FROM>D2_TO OR D1_TO<D2_FROM)
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/000601d70506%2441aae360%24c500aa20%24%40satron.de.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4Dgc1P2fC0zlg9YV%40smtp.poczta.onet.pl.
No, it not fail, eg:
D1_FROM = 10.01.2021
D1_FROM>D2_TO OR D1_TO<D2_FROM = false or false = false
Not false = true which mean overlaped
Graphical example- blue are only situation when dates are not overlaped
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAKC2Lgwi-U%2ByyKhbbgOQG%2Bywjy5Oz1xa6bmOj8ew21f%2B3BKihA%40mail.gmail.com.
I have realized it as follows
create or alter procedure P_CHECK_PERIOD2 (
BEGINDATE date, -- begin from the new entry
ENDDATE date, --end from the new entry
PKWID integer) -- the id from the car
LSTDT varchar(1000)) -- the days where the car already reserved and the user
declare variable tmpdate date;
declare variable tmpdate2 date;
declare variable von date;
declare variable bis date;
declare variable sbnr varchar(40);
lstdt = '';
tmpdate = begindate;
while (tmpdate <= enddate) do
for select a.von, a.bis, a.fahrer_name from tdienstpkw_reservierung a where (:tmpdate between cast(von as date) and cast(bis as date)) and :pkwid = id_pkw into :von, :bis, :sbnr do
lstdt = tmpdate || ' - ' || ' SBNR: ' || coalesce(:sbnr,'NULL');
tmpdate = tmpdate + 1;
Thank you 😊
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4Dghmh2lFBzm7C%40smtp.poczta.onet.pl.