Getting Unavailable dates for renting a product that has stocks

Database queries, normally so simple, yet sometimes so difficult. (brain trainer)

So I have products, stocks and rentStockOrders. These products can be rented for a set of days. The stocks also have a date when they are available. If a new product (stock) can be rented depends on the already rented stocks of that product.

A stock item cannot be rented before it's available date.
A rentStockOrder (linked between order and stocks) contains the bookings, thus rentStartDate and rentEndDate.
A product can be rented for a set of days, where the start date is not given. The product is selected, and after that a date time picker is used to select a starting day for renting.
An overall minimum and maximum date is applied (about a year ahead).

The idea is that the user didn't select the start date yet, before the user is able to do that I want to disable certain dates in a datetimepicker that cannot be used as starting date because there are no stocks available for the product's renting period.

To put it in a context: One product is selected, the user is given the option to specify a length in days he wants to rent this product (1 week, 2 weeks or 3 weeks). When the user has selected that, they must select a start date. Instead of every time showing an error that this date is not available, I rather disable start dates before hand.

Since it is more often that a product is available for renting then not, I think it is better to send a list of unavailable select dates to my website instead of a whole list of available dates. So the days that are not available cannot be clicked in a date time picker.

Most examples I have found so far include a input parameter for start and end date which I don't have, all I have a length in days a product wants to be rented and how many stocks are already rented for certain time frames.

EDIT:

As requested, test data and tables:

Stocks

+---------+-----------+-------------------+
| stockId | productId | availableFromDate |
+---------+-----------+-------------------+
|       1 |         1 | 1-01-2016         |
|       2 |         1 | 1-01-2016         |
+---------+-----------+-------------------+

RentStockOrders

+------------------+---------+----------------+----------------+
| rentStockOrderId | stockId | beginRentDate  |  endRentDate   |
+------------------+---------+----------------+----------------+
|                1 |       1 | 15-1-2016      | 14-2-2016      |
|                2 |       2 | 30-1-2016      | 20-2-2016      |
|                3 |       2 | 26-2-2016      | 7-3-2016       |
|                4 |       1 | 29-2-2016      | 14-3-2016      |
+------------------+---------+----------------+----------------+

Based on these records, I want to generate a list of unavailable dates. I've left out some columns for simplicity

Input is a day and a productId. So if I would input for days: 14 and for productId: 1 I would have some of the following expected results:

25-01-2016 (stockId 1 is already booked, and stock 2 is booked soon, 14 days not possible.
30-01-2016 (both booked)
13-02-2016 (stock 1 is not back yet)
17-02-2016 (stock 2 already booked, stock 1 will be rented in 13 days, not enough for 14).
..and a lot more where both stocks are already rented.

What I would NOT expect is for example 15-02-2016, because Stock 1 would be available for the next 14 days.

If it is to difficult, then perhaps getting the available dates is simpler and I will switch this around in code. In this example it would be less data to pull from the database, but in reality there are about 250 items of one product so getting the unavailable dates perhaps better.

I've tried this answer to get the available dates, with no success yet, no errors, just returns no data.

declare @startDate datetime, @endDate datetime, @days int
select @startDate = '2016/01/01', @endDate='2016/03/31', @days=2

select stockId, min(endRentDate)
from
    (
    select  stockId ,endRentDate,
            (select top 1 endRentDate
            from RentStockOrders sInner
            where sInner.endRentDate > sOuter.beginRentDate
                    and sInner.stockId = sOuter.stockId
                    and sInner.endRentDate between @startDate and @endDate
            order by sInner.endRentDate) as nextAvailableDate
    from    RentStockOrders sOuter
    where sOuter.beginRentDate between @startDate and @endDate
    ) sub
group by stockId, nextAvailableDate
having dateDiff(d, min(endRentDate), isNull(nextAvailableDate,dateAdd(d,1,@endDate))) >= @days

This question is pretty difficult if you want to just use SQL if not impossible. I have made an pretty optimized/performant solution using Oracle PL/SQL and SQL. You can easily translate it to TSQL. The function returns a set/collection of dates. I also made another version which returns one big string with space seperated date values like "01-01-2016 02-01-2016 03-01-2016" etc... You could also make a version which return all seperate dates as date periodstrings for example "01-01-2016/10-01-2016 15-01-2016/25-01-2016" which you can then easily send to- and parse- in your application.

set serveroutput on;

drop table Product cascade constraints;
drop table Stocks cascade constraints;
drop table RentStockOrders cascade constraints;

create table Product (
  productId     number primary key,
  description   varchar2(255)
);

create table Stocks (
  stockId           number primary key,
  productId         number references Product(productId),
  availableFromDate date
);

create table RentStockOrders (
  rentStockOrderId  number primary key,
  stockId           number references Stocks(stockId),
  beginRentDate     date,
  endRentDate       date
);

insert into Product values (1,'product 1');
insert into Product values (2,'product 2');

insert into Stocks values (1,1,to_date('01-01-2016','dd-mm-yyyy'));
insert into Stocks values (2,1,to_date('01-01-2016','dd-mm-yyyy'));
insert into Stocks values (3,2,to_date('01-01-2016','dd-mm-yyyy'));
insert into Stocks values (4,2,to_date('01-01-2016','dd-mm-yyyy'));

insert into RentStockOrders values (1,1,to_date('15-01-2016','dd-mm-yyyy'),to_date('14-02-2016','dd-mm-yyyy'));
insert into RentStockOrders values (2,2,to_date('30-01-2016','dd-mm-yyyy'),to_date('20-02-2016','dd-mm-yyyy'));
insert into RentStockOrders values (3,2,to_date('26-02-2016','dd-mm-yyyy'),to_date('07-03-2016','dd-mm-yyyy'));
insert into RentStockOrders values (4,1,to_date('29-02-2016','dd-mm-yyyy'),to_date('14-03-2016','dd-mm-yyyy'));

--insert into RentStockOrders values (5,3,to_date('15-01-2016','dd-mm-yyyy'),to_date('14-02-2016','dd-mm-yyyy'));
insert into RentStockOrders values (6,4,to_date('20-01-2016','dd-mm-yyyy'),to_date('25-01-2016','dd-mm-yyyy'));
--insert into RentStockOrders values (7,4,to_date('01-01-2016','dd-mm-yyyy'),to_date('01-04-2016','dd-mm-yyyy'));
insert into RentStockOrders values (8,3,to_date('17-01-2016','dd-mm-yyyy'),to_date('25-01-2016','dd-mm-yyyy'));


--stocks with productId X which are rented for coming year from date Y with rentPeriode Z
select *
from RentStockOrders rso, Stocks s
where rso.stockId=s.stockId
and s.productId=1
and rso.beginRentDate>=to_date('01-01-2016','dd-mm-yyyy')-14
and rso.endRentDate<=to_date('01-01-2016','dd-mm-yyyy')+365
order by beginRentDate;


create or replace package my_globals
as
  --type has to be globally declared to be used as a return type
  type t_dates is table of date INDEX BY pls_integer;

  cursor c_searchRentData(p_productid number, p_beginDate date, p_endDate date, p_rentPeriod pls_integer) is
    select beginRentDate,endRentDate
    from RentStockOrders rso, Stocks s
    where rso.stockId=s.stockId
    and s.productId=p_productid
    and rso.beginRentDate>=p_beginDate-p_rentPeriod
    and rso.endRentDate<=p_endDate
    order by beginRentDate;
end;

/

--helper function tot return more future (or larger) date of two dates
create or replace function maxDate (p_date1 date, p_date2 date)
return date
is
begin
  if p_date1>=p_date2 then
    return p_date1;
  else
    return p_date2;
  end if;
end;

/

create or replace function getBlockedDates (p_productid number, p_beginDate date, p_endDate date, p_rentPeriod pls_integer)
return my_globals.t_dates
as
  v_dates     my_globals.t_dates;
  v_begindate date;
  v_enddate   date;
  i           pls_integer;
begin
  i:=1; --collection counts from 1
  v_enddate:=p_beginDate-1;
  for r_date in my_globals.c_searchRentData(p_productid, p_beginDate, p_endDate, p_rentPeriod)
  loop
    if (v_enddate < r_date.beginRentDate) or (v_enddate < r_date.endRentDate)
    then
      --if previous enddate is bigger use that one
      v_begindate:=maxDate(r_date.beginRentDate-p_rentPeriod,v_enddate+1); --first date of blocked period
      v_enddate:=maxDate(r_date.endRentDate,v_enddate+1); --last date of blocked period

      for j in 1..v_enddate-v_begindate+1 loop
        v_dates(i):=v_begindate+j-1;
        i:=i+1;
      end loop;
    end if;
  end loop;
  return v_dates;
end;

/

create or replace function getBlockedDatesAsStr (p_productid number, p_beginDate date, p_endDate date, p_rentPeriod pls_integer)
return varchar2
as
  v_dates     varchar2(4096) := ''; --should be sufficient for one year of blocked dates
  v_begindate date;
  v_enddate   date;
  i           pls_integer;
begin
  i:=1; --collection counts from 1
  v_enddate:=p_beginDate-1;
  for r_date in my_globals.c_searchRentData(p_productid, p_beginDate, p_endDate, p_rentPeriod)
  loop
    if (v_enddate < r_date.beginRentDate) or (v_enddate < r_date.endRentDate)
    then
      --if previous enddate is bigger use that one
      v_begindate:=maxDate(r_date.beginRentDate-p_rentPeriod,v_enddate+1); --first date of blocked period
      v_enddate:=maxDate(r_date.endRentDate,v_enddate+1); --last date of blocked period

      for j in 1..v_enddate-v_begindate+1 loop
        v_dates:=v_dates||' '||to_char(v_begindate+j-1,'dd-mm-yyyy');
        i:=i+1;
      end loop;
    end if;
  end loop;
  return ltrim(v_dates);
end;

/

create or replace function FindAndSplit(haystack in out varchar2, needle in varchar2)
  return varchar2
is
  s2    varchar2(1000);
  idx   pls_integer;
begin
  --dbms_output.put_line('in:'||haystack);
  idx:=instr(haystack,needle);
  if (idx=0) then
    --return full haystack when needle not found
    s2:=haystack;
    --remaining haystack is empty
    haystack:='';
    return s2;
  end if;
  --find string left at idx
  s2:=substr(haystack,1,idx-1);
  --dbms_output.put_line('out:'||s2);
  --remaining haystack is string right at idx
  haystack:=substr(haystack,idx+1,length(haystack)-idx);
  --dbms_output.put_line('return:'||haystack);
  return s2;
end;

/

--testcases
declare
 v_dates   my_globals.t_dates;
 i          pls_integer;
begin
  --store the result of stored function in local collection
  v_dates:=getBlockedDates(1, to_date('01-01-2016','dd-mm-yyyy'), to_date('01-01-2016','dd-mm-yyyy')+365, 1);
  --iterate through collection
  FOR i IN 1 .. v_dates.count LOOP
      dbms_output.put_line('Blocked date: '||v_dates(i));
  end loop;

  dbms_output.put_line('');

  --store the result of stored function in local collection
  v_dates:=getBlockedDates(2, to_date('01-01-2016','dd-mm-yyyy'), to_date('01-01-2016','dd-mm-yyyy')+365, 1);
  --iterate through collection
  FOR i IN 1 .. v_dates.count LOOP
      dbms_output.put_line('Blocked date: '||v_dates(i));
  end loop;  
end;

/

declare
 v_dates    varchar2(4096);
 v_date     varchar2(10);
 i          pls_integer;
begin
  --store the result of stored function in local string
  v_dates:=getBlockedDatesAsStr(1, to_date('01-01-2016','dd-mm-yyyy'), to_date('01-01-2016','dd-mm-yyyy')+365, 1);
  dbms_output.put_line(v_dates);
  --iterate through string
  loop
      v_date:=FindAndSplit(v_dates,' ');
      dbms_output.put_line('Blocked date: '||v_date);
      exit when v_dates is null;
  end loop;

  dbms_output.put_line('');

  --store the result of stored function in local string
  v_dates:=getBlockedDatesAsStr(2, to_date('01-01-2016','dd-mm-yyyy'), to_date('01-01-2016','dd-mm-yyyy')+365, 1);
  --iterate through string
  loop
      v_date:=FindAndSplit(v_dates,' ');
      dbms_output.put_line('Blocked date: '||v_date);
      exit when v_dates is null;
  end loop;
end;

Recommended topics

Hot tags