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.

• 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

I would use a Calendar table. This table simply has a list of dates for several decades.

CREATE TABLE [dbo].[Calendar](
    [dt] [date] NOT NULL,
 CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED 
(
    [dt] ASC
))

In my system it has few extra columns, such as [IsLastDayOfMonth], [IsLastDayOfQuarter], which are useful in some reports, but in your case you need just the date column. There are many ways to populate such table.

For example, 100K rows (~270 years) from 1900-01-01:

INSERT INTO dbo.Calendar (dt)
SELECT TOP (100000) 
    DATEADD(day, ROW_NUMBER() OVER (ORDER BY s1.[object_id])-1, '19000101') AS dt
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1);

Sample data

DECLARE @Stocks TABLE (
    StockId int
    , ProductId int
    , AvailableFromDate date);

INSERT INTO @Stocks(StockId, ProductId, AvailableFromDate) VALUES
(1, 1, '2016-01-01'),
(2, 1, '2016-01-01');

DECLARE @RentStockOrders TABLE (
    RentStockOrderId int
    , StockId int
    , BeginRentDate date
    , EndRentDate date);

INSERT INTO @RentStockOrders (RentStockOrderId, StockId, BeginRentDate, EndRentDate) VALUES
(1, 1, '2016-01-15', '2016-02-14'),
(2, 2, '2016-01-30', '2016-02-20'),
(3, 2, '2016-02-26', '2016-03-07'),
(4, 1, '2016-02-29', '2016-03-14');

Parameters

DECLARE @ParamProductID int = 1;
DECLARE @ParamDays int = 14;
DECLARE @ParamStartDate date = '2015-12-01';
DECLARE @ParamEndDate date = '2017-01-01';
-- these dates define some reasonable limit

First variant

It turns out that window functions accept only literal constant as a size of a window, not a variable. Alas. Still, I'll show this query, because it illustrates the approach and shows how simple it would be if SQL Server supported variables as the size of the window. It also gives the correct answer that we can use to verify the second variant.

WITH
CTE_AllDays
-- list all days and stock IDs between @ParamStartDate and @ParamEndDate
-- for each day and stock indicate whether it is available based on AvailableFromDate
AS
(
    SELECT
        S.StockId
        ,dbo.Calendar.dt
        ,CASE WHEN dbo.Calendar.dt >= S.AvailableFromDate 
            THEN 1 ELSE 0 END AS AvailableStockDay
        -- 1 - available
        -- 0 - not available
    FROM
        @Stocks AS S
        INNER JOIN dbo.Calendar ON
            dbo.Calendar.dt >= @ParamStartDate
            AND dbo.Calendar.dt = @ParamStartDate
                AND dbo.Calendar.dt = R.BeginRentDate
                AND dbo.Calendar.dt 0 - available
    FROM
        CTE_AllDays
        LEFT JOIN CTE_BookedDays ON
            CTE_BookedDays.StockId = CTE_AllDays.StockId AND
            CTE_BookedDays.dt = CTE_AllDays.dt
    GROUP BY
        CTE_AllDays.dt
)
,CTE_Sum
-- rolling sum of flags with 14 days window
AS
(
    SELECT
        dt
        ,SUM(CTE_Daily.AvailableDay) OVER (ORDER BY CTE_Daily.dt
            ROWS BETWEEN CURRENT ROW AND 13 FOLLOWING) AS AvailableConsecutive
    -- we can't put @ParamDays here instead of constant
    FROM CTE_Daily
)
-- If a rolling sum =  14, 
-- it means that all 14 consecutive days are available

-- If a rolling sum <> 14, 
-- it means that at least one of the 14 consecutive days is not available
SELECT dt
FROM CTE_Sum
WHERE AvailableConsecutive <> 14
-- we can put @ParamDays here instead of 14, but not above
ORDER BY dt;

Result

Note, I started the range of dates from 2015-12-01, before the general availability.

+------------+
|     dt     |
+------------+
| 2015-12-01 |
| 2015-12-02 |
| 2015-12-03 |
| 2015-12-04 |
| 2015-12-05 |
| 2015-12-06 |
| 2015-12-07 |
| 2015-12-08 |
| 2015-12-09 |
| 2015-12-10 |
| 2015-12-11 |
| 2015-12-12 |
| 2015-12-13 |
| 2015-12-14 |
| 2015-12-15 |
| 2015-12-16 |
| 2015-12-17 |
| 2015-12-18 |
| 2015-12-19 |
| 2015-12-20 |
| 2015-12-21 |
| 2015-12-22 |
| 2015-12-23 |
| 2015-12-24 |
| 2015-12-25 |
| 2015-12-26 |
| 2015-12-27 |
| 2015-12-28 |
| 2015-12-29 |
| 2015-12-30 |
| 2015-12-31 |
| 2016-01-17 |
| 2016-01-18 |
| 2016-01-19 |
| 2016-01-20 |
| 2016-01-21 |
| 2016-01-22 |
| 2016-01-23 |
| 2016-01-24 |
| 2016-01-25 |
| 2016-01-26 |
| 2016-01-27 |
| 2016-01-28 |
| 2016-01-29 |
| 2016-01-30 |
| 2016-01-31 |
| 2016-02-01 |
| 2016-02-02 |
| 2016-02-03 |
| 2016-02-04 |
| 2016-02-05 |
| 2016-02-06 |
| 2016-02-07 |
| 2016-02-08 |
| 2016-02-09 |
| 2016-02-10 |
| 2016-02-11 |
| 2016-02-12 |
| 2016-02-13 |
| 2016-02-14 |
| 2016-02-16 |
| 2016-02-17 |
| 2016-02-18 |
| 2016-02-19 |
| 2016-02-20 |
| 2016-02-21 |
| 2016-02-22 |
| 2016-02-23 |
| 2016-02-24 |
| 2016-02-25 |
| 2016-02-26 |
| 2016-02-27 |
| 2016-02-28 |
| 2016-02-29 |
| 2016-03-01 |
| 2016-03-02 |
| 2016-03-03 |
| 2016-03-04 |
| 2016-03-05 |
| 2016-03-06 |
| 2016-03-07 |
| 2016-12-20 |
| 2016-12-21 |
| 2016-12-22 |
| 2016-12-23 |
| 2016-12-24 |
| 2016-12-25 |
| 2016-12-26 |
| 2016-12-27 |
| 2016-12-28 |
| 2016-12-29 |
| 2016-12-30 |
| 2016-12-31 |
| 2017-01-01 |
+------------+

Second variant

The first part of the

StackExchange Database Administrators Q#122820, answer score: 4