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.
- The query should check what dates are not available when all stocks have been rented out.
- A product can be rented for a set of days, where the start/end date is not given. The product is selected, and after that a date time picker is used to select a starting day for renting.
- there is a overall maximum date of one year ahead (can be input parameter) and a minimum date of today (or +2).
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 select a list of unavailable select dates from my database 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.
Stocks
+---------+-----------+-------------------+
| stockId | productId | availableFromDate |
+---------+-----------+-------------------+
| 1 | 1 | 01-01-2016 |
| 2 | 1 | 01-01-2016 |
+---------+-----------+-------------------+
RentStockOrders
+------------------+---------+----------------+----------------+
| rentStockOrderId | stockId | beginRentDate | endRentDate |
+------------------+---------+----------------+----------------+
| 1 | 1 | 15-01-2016 | 14-02-2016 |
| 2 | 2 | 30-01-2016 | 20-02-2016 |
| 3 | 2 | 26-02-2016 | 07-03-2016 |
| 4 | 1 | 29-02-2016 | 14-03-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 too 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 been trying to get this to work to get at least the available dates, with no success so far, returns no records:
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
original source, to be honest, I don't understand the last part and what this query is actually doing.