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;
