Sql-server – Getting Unavailable dates for renting a product that has stocks

sql serversql-server-2012

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.

Best Answer

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 <= @ParamEndDate
    WHERE
        S.ProductId = @ParamProductID
)
,CTE_BookedDays
-- list all booked (unavailable) days for each stock ID
AS
(
    SELECT
        S.StockId
        ,CA.dt
        ,0 AS AvailableStockDay
        -- 0 - not available
    FROM
        @RentStockOrders AS R
        INNER JOIN @Stocks AS S ON S.StockId = R.StockId
        CROSS APPLY
        (
            SELECT dbo.Calendar.dt
            FROM dbo.Calendar
            WHERE
                dbo.Calendar.dt >= @ParamStartDate
                AND dbo.Calendar.dt <= @ParamEndDate
                AND dbo.Calendar.dt >= R.BeginRentDate
                AND dbo.Calendar.dt <= R.EndRentDate
        ) AS CA
    WHERE
        S.ProductId = @ParamProductID
)
,CTE_Daily
-- combine individual availability flags
-- first: multiply to get the final availability for a stock ID and day
-- second: group further by day and SUM flags
AS
(
    SELECT
        CTE_AllDays.dt
        ,CASE WHEN 
            SUM(
            CTE_AllDays.AvailableStockDay * ISNULL(CTE_BookedDays.AvailableStockDay, 1)
            ) = 0 
        THEN 0 ELSE 1 END AS AvailableDay
        -- day is available, if any stock is available
        -- SUM=0 - not available
        -- SUM>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 query until CTE_Daily is the same. Then, I'll use a gaps-and-islands approach to find the islands of available dates and calculate their sizes.

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 <= @ParamEndDate
    WHERE
        S.ProductId = @ParamProductID
)
,CTE_BookedDays
-- list all booked (unavailable) days for each stock ID
AS
(
    SELECT
        S.StockId
        ,CA.dt
        ,0 AS AvailableStockDay
        -- 0 - not available
    FROM
        @RentStockOrders AS R
        INNER JOIN @Stocks AS S ON S.StockId = R.StockId
        CROSS APPLY
        (
            SELECT dbo.Calendar.dt
            FROM dbo.Calendar
            WHERE
                dbo.Calendar.dt >= @ParamStartDate
                AND dbo.Calendar.dt <= @ParamEndDate
                AND dbo.Calendar.dt >= R.BeginRentDate
                AND dbo.Calendar.dt <= R.EndRentDate
        ) AS CA
    WHERE
        S.ProductId = @ParamProductID
)
,CTE_Daily
-- combine individual availability flags
-- first: multiply to get the final availability for a stock ID and day
-- second: group further by day and SUM flags
AS
(
    SELECT
        CTE_AllDays.dt
        ,CASE WHEN 
            SUM(
            CTE_AllDays.AvailableStockDay * ISNULL(CTE_BookedDays.AvailableStockDay, 1)
            ) = 0 
        THEN 0 ELSE 1 END AS AvailableDay
        -- day is available, if any stock is available
        -- SUM=0 - not available
        -- SUM>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_RowNumbers
-- calculate two sets of row numbers to isolate consecutive rows with 0s and 1s 
-- (gaps and islands)
AS
(
    SELECT
        dt
        ,AvailableDay
        ,ROW_NUMBER() OVER (ORDER BY dt) AS rn1
        ,ROW_NUMBER() OVER (PARTITION BY AvailableDay ORDER BY dt) AS rn2
    FROM CTE_Daily
)
,CTE_Groups
-- each gaps and island will have the same GroupNumber
-- count the size of the Group
-- number the rows within each Group to find if @ParamDays rows fit into the Group
AS
(
    SELECT
        dt
        ,AvailableDay
        ,GroupNumber
        ,COUNT(*) OVER (PARTITION BY GroupNumber) AS GroupSize
        ,ROW_NUMBER() OVER (PARTITION BY GroupNumber ORDER BY dt) AS GroupRN
    FROM
        CTE_RowNumbers
        CROSS APPLY (SELECT rn1 - rn2 AS GroupNumber) AS CA
)
SELECT
    dt
    --,CASE WHEN AvailableDay = 1 AND GroupSize - GroupRN + 1 >= @ParamDays
    --THEN 1 ELSE 0 END AS AvailableConsecutive
FROM CTE_Groups
WHERE
    CASE WHEN AvailableDay = 1 AND GroupSize - GroupRN + 1 >= @ParamDays
    THEN 1 ELSE 0 END = 0
    -- AvailableConsecutive = 0 to list all unavailable days
ORDER BY dt;

The result is the same as in the first variant, but this variant uses parameter @ParamDays.

To understand how it works, run the query starting with the first CTE, examine the results, then add the next CTE, examine the results and so on.

Related Question