SQL Date Range Query - Compare Tables

Question

I have two SQL Server tables containing the following information:

t_venues table:

venue_id is unique

 venue_id | start_date | end_date 1 | 01/01/2014 | 02/01/2014 2 | 05/01/2014 | 05/01/2014 3 | 09/01/2014 | 15/01/2014 4 | 20/01/2014 | 30/01/2014 

t_venueuser table:

venue_id not unique

 venue_id | start_date | end_date 1 | 02/01/2014 | 02/01/2014 2 | 05/01/2014 | 05/01/2014 3 | 09/01/2014 | 10/01/2014 4 | 23/01/2014 | 25/01/2014 

From these two tables, I need to find dates that were not selected for each range, so the result will look like this:

 venue_id | start_date | end_date 1 | 01/01/2014 | 01/01/2014 3 | 11/01/2014 | 15/01/2014 4 | 20/01/2014 | 22/01/2014 4 | 26/01/2014 | 30/01/2014 

I can compare the two tables and get the date ranges from t_venues so that they appear in my query using "except", but I cannot get a query to create unselected dates. Any help would be appreciated.

Answer 1

Calendar table!

Another perfect candidate for a calendar table. If you can not find it, here, which I did earlier .

Setting data

 DECLARE @t_venues table ( venue_id int , start_date date , end_date date ); INSERT INTO @t_venues (venue_id, start_date, end_date) VALUES (1, '2014-01-01', '2014-01-02') , (2, '2014-01-05', '2014-01-05') , (3, '2014-01-09', '2014-01-15') , (4, '2014-01-20', '2014-01-30') ; DECLARE @t_venueuser table ( venue_id int , start_date date , end_date date ); INSERT INTO @t_venueuser (venue_id, start_date, end_date) VALUES (1, '2014-01-02', '2014-01-02') , (2, '2014-01-05', '2014-01-05') , (3, '2014-01-09', '2014-01-10') , (4, '2014-01-23', '2014-01-25') ; 

Inquiry

 SELECT t_venues.venue_id , calendar.the_date , CASE WHEN t_venueuser.venue_id IS NULL THEN 1 ELSE 0 END As is_available FROM dbo.calendar /* see: http://gvee.co.uk/files/sql/dbo.numbers%20&%20dbo.calendar.sql for an example */ INNER JOIN @t_venues As t_venues ON t_venues.start_date <= calendar.the_date AND t_venues.end_date >= calendar.the_date LEFT JOIN @t_venueuser As t_venueuser ON t_venueuser.venue_id = t_venues.venue_id AND t_venueuser.start_date <= calendar.the_date AND t_venueuser.end_date >= calendar.the_date ORDER BY t_venues.venue_id , calendar.the_date ; 

Result

 venue_id the_date is_available ----------- ----------------------- ------------ 1 2014-01-01 00:00:00.000 1 1 2014-01-02 00:00:00.000 0 2 2014-01-05 00:00:00.000 0 3 2014-01-09 00:00:00.000 0 3 2014-01-10 00:00:00.000 0 3 2014-01-11 00:00:00.000 1 3 2014-01-12 00:00:00.000 1 3 2014-01-13 00:00:00.000 1 3 2014-01-14 00:00:00.000 1 3 2014-01-15 00:00:00.000 1 4 2014-01-20 00:00:00.000 1 4 2014-01-21 00:00:00.000 1 4 2014-01-22 00:00:00.000 1 4 2014-01-23 00:00:00.000 0 4 2014-01-24 00:00:00.000 0 4 2014-01-25 00:00:00.000 0 4 2014-01-26 00:00:00.000 1 4 2014-01-27 00:00:00.000 1 4 2014-01-28 00:00:00.000 1 4 2014-01-29 00:00:00.000 1 4 2014-01-30 00:00:00.000 1 (21 row(s) affected) 

Explanation

Our calendar tables contain an entry for each date.

We join our t_venues (alternatively, if you have a choice, lose the t_ prefix) so that it returns between our start_date and end_date every day. Sample output for venue_id=4 for this connection only:

 venue_id the_date ----------- ----------------------- 4 2014-01-20 00:00:00.000 4 2014-01-21 00:00:00.000 4 2014-01-22 00:00:00.000 4 2014-01-23 00:00:00.000 4 2014-01-24 00:00:00.000 4 2014-01-25 00:00:00.000 4 2014-01-26 00:00:00.000 4 2014-01-27 00:00:00.000 4 2014-01-28 00:00:00.000 4 2014-01-29 00:00:00.000 4 2014-01-30 00:00:00.000 (11 row(s) affected) 

Now we have one row per day, we [external] join our table t_venueuser . We join this almost the same way as before, but with one twist added: we also need to join venue_id !

Running this for venue_id=4 gives the following result:

 venue_id the_date t_venueuser_venue_id ----------- ----------------------- -------------------- 4 2014-01-20 00:00:00.000 NULL 4 2014-01-21 00:00:00.000 NULL 4 2014-01-22 00:00:00.000 NULL 4 2014-01-23 00:00:00.000 4 4 2014-01-24 00:00:00.000 4 4 2014-01-25 00:00:00.000 4 4 2014-01-26 00:00:00.000 NULL 4 2014-01-27 00:00:00.000 NULL 4 2014-01-28 00:00:00.000 NULL 4 2014-01-29 00:00:00.000 NULL 4 2014-01-30 00:00:00.000 NULL (11 row(s) affected) 

See how we have NULL for rows where there is no t_venueuser . Genius, no? ;-)

So, in my first query, I gave you a quick CASE statement showing availability (1 = available, 0 = not available). This is for illustration purposes only, but may be useful to you.

Then you can either wrap the query and then apply an additional filter in this computed column or simply add the where WHERE t_venueuser.venue_id IS NULL to: WHERE t_venueuser.venue_id IS NULL , and this will do the same trick.

Answer 2

This is a complete hack, but it gives the results you need, I only tested them on the data that you provided so that there could be gotchas with large sets.

In general, what you are trying to solve here is the problem of problems with spaces and islands, this is (briefly) a sequence in which some elements are missing. Missed items are spaces and existing items are islands. If you want to understand this problem, generally check out a few articles:

• Simple discussion article
• blogs.MSDN article
• SO meets marked spaces and islands

The code:

 ;with dates as ( SELECT vdates.venue_id, vdates.vdate FROM ( SELECT DATEADD(d,sv.number,v.start_date) vdate , v.venue_id FROM t_venues v INNER JOIN master..spt_values sv ON sv.type='P' AND sv.number BETWEEN 0 AND datediff(d, v.start_date, v.end_date)) vdates LEFT JOIN t_venueuser vu ON vdates.vdate >= vu.start_date AND vdates.vdate <= vu.end_date AND vdates.venue_id = vu.venue_id WHERE ISNULL(vu.venue_id,-1) = -1 ) SELECT venue_id, ISNULL([1],[2]) StartDate, [2] EndDate FROM (SELECT venue_id, rDate, ROW_NUMBER() OVER (PARTITION BY venue_id, DateType ORDER BY rDate) AS rType, DateType as dType FROM( SELECT d1.venue_id ,d1.vdate AS rDate ,'1' AS DateType FROM dates AS d1 LEFT JOIN dates AS d0 ON DATEADD(d,-1,d1.vdate) = d0.vdate LEFT JOIN dates AS d2 ON DATEADD(d,1,d1.vdate) = d2.vdate WHERE CASE ISNULL(d2.vdate, '01 Jan 1753') WHEN '01 Jan 1753' THEN '2' ELSE '1' END = 1 AND ISNULL(d0.vdate, '01 Jan 1753') = '01 Jan 1753' UNION SELECT d1.venue_id ,ISNULL(d2.vdate,d1.vdate) ,'2' FROM dates AS d1 LEFT JOIN dates AS d2 ON DATEADD(d,1,d1.vdate) = d2.vdate WHERE CASE ISNULL(d2.vdate, '01 Jan 1753') WHEN '01 Jan 1753' THEN '2' ELSE '1' END = 2 ) res ) src PIVOT (MIN (rDate) FOR dType IN ( [1], [2] ) ) AS pvt 

Results:

 venue_id StartDate EndDate 1 2014-01-01 2014-01-01 3 2014-01-11 2014-01-15 4 2014-01-20 2014-01-22 4 2014-01-26 2014-01-30