Check if a date is available between ranges-CodePudding

I have a department rental system. So users can rent a department and dates will not be available on the site.

So I can have a department with some dates like:

Starting Date: '2022-04-11' EndingDate '2022-04-14'

and Starting Date '2022-04-16' EndingDate '2022-04-18'

So, as you can see that department has available day '2022-04-15'

I want a select statement bit true or false if a date is available between two dates, so if my dates are

'2022-04-11' and '2022-04-18', it should return a true value because '2022-04-15' it's available. How can I achieve that? Regards

CodePudding user response：

You need to also generate a table of dates - so that you have a row in your DATES table for each day.

You can then JOIN your DATES table to your main table with an Outer Join and check for NULLS on the main table side to verify.

-- Using your sample values, create a RENTAL table
CREATE TABLE Rentals (
    RentalID INT IDENTITY(1,1),
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL
) ;

INSERT Rentals (StartDate, EndDate)
VALUES ('2022-04-11', '2022-04-14'), ('2022-04-16', '2022-04-18')
GO

-- Create the DATES table
CREATE TABLE DatesTable (
    DateName DATE NOT NULL
);
GO
-- Populate it for this year
DECLARE @inc INT = 365

WHILE @inc > 0
BEGIN
    INSERT DatesTable (DateName)
    SELECT DATEADD(DAY, @inc, '2021-12-31') ;
    
    SELECT @inc = @inc - 1 ;
END
GO

SELECT d.DateName, CASE WHEN r.StartDate IS NULL THEN 'TRUE' ELSE 'FALSE' END as IsAvailable
FROM DatesTable d
LEFT JOIN Rentals r ON d.DateName >= r.StartDate AND d.DateName <= r.EndDate
WHERE d.DateName BETWEEN '2022-04-11' and '2022-04-18'
ORDER BY d.[DateName] ASC

CodePudding user response：

DepartmentTable:
name
start_date
end_date

parameter
@bookingDate

select count(*) from 
Department
where @bookingDate between start_date and end_date

If you get count greater than 0 then its not available

CodePudding user response：

I think you can use window functions to achieve what you want. I'm not familiar with SQL Server but I googled and it seems to support my query (which I wrote in Postgres).

create temporary table rentals (
  id serial primary key,
  starts_at date,
  ends_at date
);

insert into rentals (starts_at, ends_at) values 
('2022-01-05', '2022-01-6'),
('2022-01-08', '2022-01-14'),
('2022-01-15', '2022-01-16'),
('2022-01-18', '2022-01-20');


with r as (
    select id,
    starts_at,
    ends_at,
    lead(starts_at, 1) over(order by starts_at asc) as next_starts_at
  from rentals         
) 
select 
   ( 
      CASE count(id)
      WHEN 0 THEN false 
      ELSE true      
      END
    ) as available 
from r
where 
      ( ends_at   interval '1 day' < next_starts_at OR next_starts_at IS NULL ) AND 
      ends_at >= '2022-01-03' AND
      ends_at < '2022-01-05'

The above first creates a CTE using the window function lead to find the next start date for any given row.


select   id,
         starts_at,
         ends_at,
         lead(starts_at, 1) over(order by starts_at asc) as next_starts_at
  from rentals         

 id | starts_at  |  ends_at   | next_starts_at
---- ------------ ------------ ----------------
  1 | 2022-01-05 | 2022-01-06 | 2022-01-08
  2 | 2022-01-08 | 2022-01-14 | 2022-01-15
  3 | 2022-01-15 | 2022-01-16 | 2022-01-18
  4 | 2022-01-18 | 2022-01-20 |

Then it selects dates between the range you want

ends_at >= '2022-01-03' AND
ends_at < '2022-01-05'

and also selects rentals which

have at least a one day gap between their end date and the next rental's start date

( ends_at interval '1 day' < next_starts_at )

or are the last rental recorded in the system and thus there is definitely availability after this last rental

OR next_starts_at IS NULL

I tested this on a range of dates and it seems to work assuming I understand your requirements correctly.