Getting Inaccessible Dates for Renting a Product with Stocks

Question

Database queries are usually so simple, but sometimes so complex. (brain trainer)

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

• A product cannot be rented until its availability.
• A rentStockOrder (linked between order and stocks) contains orders, thus rentStartDate and rentEndDate .
• The product can be rented for several days when the start date is not specified. The product is selected, and after that, the time selection date is selected to select the day the rental starts.
• The general minimum and maximum date applies (approximately one year in advance).

The idea is that the user has not yet selected a start date, before the user can do this, I want to disable certain dates in the datetimepicker, which cannot be used as the start date, because there are no available stocks for the rental period of the product.

To express this in the context: one product is selected, the user is given the opportunity to specify the length in days that he wants to rent for this product (1 week, 2 weeks or 3 weeks). When the user has selected this, they must select a start date. Instead of showing an error every time that this date is unavailable, I rather turn off the start dates before the start.

Since the product is most often available for rent, then no, I think it is better to send a list of inaccessible dates to my site, rather than the entire list of available dates. Thus, days that are not available cannot be pressed at the time of the selection of time.

Most of the examples that I have found so far include an input parameter for the start and end dates, which I don’t have, all that I have for a few days when the product wants to be leased and how many shares are already leased for certain temporary framework.

EDIT:

As requested, check the 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 entries, I want to create a list of missing dates. For convenience, I have selected several columns

Input is the day and productId. Therefore, if I entered for days: 14 and for productId: 1, I would have the following expected results:

• 01-01-2016 (reserve 1 has already been booked, and reserve 2 has been booked soon, 14 days is not possible.
• 01-01-2016 (both booked)
• 02/13/2016 (stock 1 has not yet returned)
• 02-17-2016 (stock 2 has already been booked, stock 1 will be rented in 13 days, this is not enough for 14).
• .. and much more, where both shares have already been leased.

I would not expect, for example, 15-02-2016, because Stock 1 will be available over the next 14 days.

If this is complicated, then perhaps getting the dates available is easier and I will switch this in code. In this example, there will be less data from the database, but in fact there are about 250 units of one product, so it might be better to get inaccessible dates.

I tried this answer to get the available dates, until I had time, no errors, just did not return any 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 

Answer 1

This question is quite difficult if you want to just use SQL, if not impossible. I made a pretty optimized / efficient solution using Oracle PL / SQL and SQL. You can easily translate it into TSQL. The function returns a set / set of dates. I also made another version that returns one large string with time separator values, such as "01-01-2016 02-01-2016 03-01-2016", etc ... You can also make a version, which returns all individual dates as date periods, for example, "01-01-2016 / 10-01-2016 15-01-2016 / 25-01-2016", which can then be easily sent and analyzed - 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;