date period

18 views
Skip to first unread message

Check_Mail

unread,
Feb 17, 2021, 3:24:08 AMFeb 17
to firebird...@googlegroups.com

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

unread,
Feb 17, 2021, 7:03:28 AMFeb 17
to firebird...@googlegroups.com

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

unread,
Feb 17, 2021, 7:29:43 AMFeb 17
to firebird...@googlegroups.com
select c.* -- available cars
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

Karol Bieniaszewski

unread,
Feb 17, 2021, 10:37:47 AMFeb 17
to firebird...@googlegroups.com

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

Check_Mail

unread,
Feb 23, 2021, 4:02:22 AMFeb 23
to firebird...@googlegroups.com

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 😊

image001.png
Reply all
Reply to author
Forward
0 new messages