title: How to determine availability of rentals for back to back use using a recursive cte
body: <p>I'm working on a proc that will determine whether or not an entity has enough rental equipment to cover all of its rentals during a set period of time. </p>
  
  <p>We also have a new feature that allows customers to pickup their equipment while the location is closed. </p>
  
  <p>However, we also allow back to back rentals, meaning a single piece of equipment can have multiple rentals while the entity is closed. </p>
  
  <p>I need a way to determine (Assuming all rentals are picked up and returned on time) whether or not there is enough equipment to cover all reservations. </p>
  
  <p>I attempted to use a recursive CTE to determine this, but I am not able to push my max recursion limit any further, and as some rentals are less than an hour, I must measure the dates in a maximum of 30 minute blocks. </p>
  
  <p>Though, it is also my first time using a recursive cte so I may have made a mistake. </p>
  
  <p>My example is as follows:</p>
  
  <pre><code>ID              PickupTime                      DropOffTime
  ---     -------------------------------------    ------------------------------------
  1    2019-2-28 23:00:00.000 2019-3-01 00:00:00.000
  2    2019-3-01 00:00:00.000 2019-3-01 01:00:00.000
  3    2019-3-01 04:00:00.000 2019-3-01 07:00:00.000
  4    2019-2-28 22:00:00.000 2019-2-28 23:00:00.000
  5    2019-2-2819:00:00.000  2019-2-28 21:00:00.000
  6    2019-2-28 20:00:00.000 2019-2-28 22:00:00.000
  7    2019-2-28 23:00:00.000 2019-3-01 01:00:00.000
  8    2019-2-28 23:00:00.000 2019-3-01 01:00:00.000
  9    2019-3-01 00:00:00.000 2019-3-01 02:00:00.000
  10  2019-2-28 21:00:00.000  2019-2-28 22:00:00.000
  11  2019-2-28 22:00:00.000  2019-2-28 23:00:00.000
  
  And Equipment
  
  ID    EquipmentNumber
  --    ---------------
  1     AB123
  2     AC321
  3     BL854
  </code></pre>
  
  <p>Due to figuring this out on paper, I know that a minimum of 4 equipment is needed to cover these. So this should return a boolean stating yes or no (In this case a no) if all reservations are able to be covered.</p>
  
  <p>This is what I tried:</p>
  
  <pre><code>    DECLARE @MinprefDate DATETIME = (SELECT MIN(PreferredPickupDate) FROM #Results)
      DECLARE @MaxprefDate DATETIME = (SELECT MAX(PreferredPickupDate) FROM #Results)
      DECLARE @MinexpDate DATETIME = (SELECT MIN(ExpectedReceiveDate) FROM #Results)
      DECLARE @MaxexpDate DATETIME = (SELECT MAX(ExpectedReceiveDate) FROM #Results)
  
      ;WITH CTE AS (
                      SELECT 1 AS n, r.ID, r.ContractID, @MinprefDate AS PreferredPickupDate, @MinexpDate AS ExpectedReceiveDate, r.PreviousPickup, r.PreviousExpected, r.NextPickup, r.NextExpected 
                      FROM #Results r
  
                      UNION ALL
  
                      SELECT n + 1, ID, ContractID, DATEADD(MINUTE, 1, @MinprefDate), DATEADD(MINUTE, 1, @MaxexpDate), PreviousPickup, PreviousExpected, NextPickup, NextExpected FROM CTE
                      WHERE ExpectedReceiveDate &lt;= @MaxexpDate
                      )
      SELECT * FROM CTE OPTION (MaxRecursion 3000)
  </code></pre>