date period
Check_Mail
Hello @ll,
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.
For example.
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?
Thank you.
Karol Bieniaszewski
>> 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)
Regards,
Karol Bieniaszewski
--
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.
Svein Erling Tysvær
from car c
where not exists( select * from CarReservations r
where c.car = r.car
and r.CarReservedFrom between :UserBWantsFrom and :UserBWantsTo ) -- Problem A
and not exists( select * from CarReservations r
where c.car = r.car
and r.CarReservedTo between :UserBWantsFrom and :UserBWantsTo ) -- Problem B
and not exists( select * from CarReservations r
where c.car = r.car
and :UserBWantsFrom between r.CarReservedFrom and r.CarReservedTo ) -- Problem C
and not exists( select * from CarReservations r
where c.car = r.car
and :UserBWantsTo between r.CarReservedFrom and r.CarReservedTo ) -- Problem D
Your example will fail the check for Problem B and Problem D (you still need all these four checks since it would also be a problem if a period was completely covered as it would be if UserB wanted the car 16.12.2020-15.01.2021 or 08.01.2021-09.01.2021)
HTH,
Set
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4Dgc1P2fC0zlg9YV%40smtp.poczta.onet.pl.
Karol Bieniaszewski
No, it not fail, eg:
D1_FROM = 10.01.2021
D1_TO=20.01.2021
D2_FROM=14.01.2021
D2_TO=17.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
regards,
Karol Bieniaszewski
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAKC2Lgwi-U%2ByyKhbbgOQG%2Bywjy5Oz1xa6bmOj8ew21f%2B3BKihA%40mail.gmail.com.
Check_Mail
Hello,
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
returns (
LSTDT varchar(1000)) -- the days where the car already reserved and the user
AS
declare variable tmpdate date;
declare variable tmpdate2 date;
declare variable von date;
declare variable bis date;
declare variable sbnr varchar(40);
begin
lstdt = '';
tmpdate = begindate;
if(enddate>begindate) then
begin
while (tmpdate <= enddate) do
begin
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
begin
lstdt = tmpdate || ' - ' || ' SBNR: ' || coalesce(:sbnr,'NULL');
suspend;
end
tmpdate = tmpdate + 1;
end
end
end
Thank you 😊
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4Dghmh2lFBzm7C%40smtp.poczta.onet.pl.
