SQL Query On Getting Available Date

Question

CREATE TABLE ReservedServiceItems
(
   serviceItemID     VARCHAR(4)      NOT NULL, 
   reservationNumber VARCHAR(4)      NOT NULL, 
   startDate         DATE            NOT NULL, 
   endDate           DATE            NOT NULL, 
   qty               INT             NOT NULL,
);

INSERT INTO ReservedServiceItems VALUES('SCI1','R1','2016-02-02','2016-02-08','1');   --Availalbe 09-11
INSERT INTO ReservedServiceItems VALUES('SCI1','R2','2016-02-12','2016-02-15','1');   --Available 16--end of query
INSERT INTO ReservedServiceItems VALUES('SCI2','R3','2016-03-10','2016-03-15','1');
INSERT INTO ReservedServiceItems VALUES('SCI3','R4','2016-04-02','2016-02-15','1');
INSERT INTO ReservedServiceItems VALUES('SCI4','R5','2016-05-10','2016-02-15','1');

I need help on writing a query that which i will input the parameter of a date range (begin and end), which will use to run against the table 'ReservedServiceItems' to check the availability of each service item and it's date range of availability.

Lets take the first 2 dummy data for example, input parameter Begin date: 01-02-2016 and End Date: 30-01-2016 the sql query should output the following data

Service Item        Available From        Available Till
SCI1                2016-02-01            2016-02-01
SCI1                2016-02-09            2016-02-11
SCI1                2016-02-16            2016-02-30

Update:

Running it in sql. What i am trying to get is the available date which SCI1 is not reserved, within the range of 2016-01-01 to 2016-01-30

Right now SCI1 is reserved on 2016-02-02 to 2016-02-08, and 2016-02-12 to 2016-02-15

I need to display the rest of the date which SCI1 is not reserved.

Any help would be appreciate =)

Answer 1

I think it helps if you can get a set of all the days in your date range, and then determine which of those days an item is in use (and conversely, which days the item is available). You can do this with a date table. I used a CTE for this like so (you might want to add a permanent date table to your database if performance is an issue):

With DateSequence(Date) as
(
    Select @beginDate as Date
        union all
    Select dateadd(day, 1, Date)
        from DateSequence
        where Date < @endDate
)

Now cross join those dates with your ReservedServiceItems table. You're looking for dates that are between the start and end reservation dates for each item, which you can identify with a CASE statement:

CASE when startDate <= Date and endDate >= Date then 1 else 0 END in_use

If your item has multiple reservations (as SCI1 does in your example), you'll get multiple rows for each date, but you really only care if at least one of those rows indicates that the item is in use on that date. So grab the MAX of that CASE statement, and group the results by item and date. The query looks like this:

SELECT d.Date,
r.ServiceItemID,
MAX(CASE when r.startDate <= d.Date and r.endDate >= d.Date then 1 else 0 END) in_use    
from DateSequence d 
cross join ReservedServiceItems r
group by d.Date, r.serviceitemid

This almost gives you what you need. The results look like this:

Date        Item    in_use
2016-02-01  SCI1    0
2016-02-02  SCI1    1
2016-02-03  SCI1    1
2016-02-04  SCI1    1
2016-02-05  SCI1    1
2016-02-06  SCI1    1
2016-02-07  SCI1    1
2016-02-08  SCI1    1
2016-02-09  SCI1    0
2016-02-10  SCI1    0
2016-02-11  SCI1    0
2016-02-12  SCI1    1
2016-02-13  SCI1    1
2016-02-14  SCI1    1
2016-02-15  SCI1    1

You can see that the subsets of rows where in_use=0 show the available dates. What you want is the min and max of the dates within each subset. You need a way to uniquely identify each subset. I found this part a little tricky, but I was able to do it with a couple window functions. If you do a ROW_NUMBER() over the items, and a DENSE_RANK() over the in_use column, you can subtract one from the other to get a value that is unique to each subset. The query now looks like:

SELECT d.Date,
r.ServiceItemID,
MAX(CASE when r.startDate <= d.Date and r.endDate >= d.Date then 1 else 0 END) in_use,    
row_number() over (partition by r.serviceitemid order by d.date, r.serviceitemid) -
dense_rank() over (partition by r.serviceitemid, max(case when r.startdate<= d.Date and r.enddate >= d.Date then 1 else 0 end) order by d.date, r.serviceitemid) group_id
from DateSequence d 
cross join ReservedServiceItems r
group by d.Date, r.serviceitemid

and the results:

Date        Item    in_use    group_id
2016-02-01  SCI1    0         0
2016-02-02  SCI1    1         1
2016-02-03  SCI1    1         1
2016-02-04  SCI1    1         1
2016-02-05  SCI1    1         1
2016-02-06  SCI1    1         1
2016-02-07  SCI1    1         1
2016-02-08  SCI1    1         1
2016-02-09  SCI1    0         7
2016-02-10  SCI1    0         7
2016-02-11  SCI1    0         7
2016-02-12  SCI1    1         4
2016-02-13  SCI1    1         4
2016-02-14  SCI1    1         4
2016-02-15  SCI1    1         4

It doesn't really matter what the group_id is, just that it's unique for each subset for each item. Now you can find the min and max date within each group. I put the previous query in another CTE called datesInUse and did a final query:

select distinct d.serviceitemid,
(select min(date) from datesinuse d2 where d2.serviceitemid = d.serviceitemid and d2.group_id = d.group_id) fromdate,
(select max(date) from datesinuse d2 where d2.serviceitemid = d.serviceitemid and d2.group_id = d.group_id) todate
from datesinuse d
where inuse = 0
order by serviceitemid

You can see a working example here: http://sqlfiddle.com/#!3/71d70/6

Answer 2

The code seems to be fine, are you looking to just alias this in a SQL view or something?

Answer 3

If I understood your question, it should simply be something like

select * from ReservedServiceItems 
       where startDate >= yourStartDate and endDate <= yourEndDate

Answer 4

The query you are looking for, will look something like this:

SELECT
     serviceItemID AS `Service Item`, 
     startDate AS `Available From`,
     endDate AS `Available Till`
FROM 
     ReservedServiceItems
WHERE
     startDate >= '2016-02-01' AND endDate <= '2016-02-29'