0

I've been banging my head up against a wall over this for a couple of hours now.

I'm working on a resource scheduling system for my company. I'm trying to come up with a SQL query that will allow me to figure out when a resource is available for an event.

I think the best way to describe this is with an example so here goes:

Given table Availability with a start and end date

TABLE Availability
| id   | resourceId | startDate  | endDate    |
| 100  | 10000      | 2014-04-15 | 2014-05-31 |
| 101  | 10001      | 2014-04-01 | 2014-05-04 |
| 102  | 10001      | 2014-05-05 | 2014-05-10 |
| 103  | 10002      | 2014-04-05 | 2014-05-05 |
| 104  | 10002      | 2014-05-07 | 2014-05-31 |

And give that I need to find all resources that are available from 2014-05-04 - 2014-05-07

With the given data, the total number of available resources is 2.

Resource 10000 is available obviously because its start and end dates span the needed range.

Resource 10001 is available because it has two entries that end and then begin on consecutive days.

Resource 10002 is NOT available because there is a gap in its availability on 2014-05-06.

Arriving at 10000 being available is easy because the simple query SELECT * FROM availability WHERE startDate <= '2014-05-04' AND endDate >= '2014-05-07' will yield the desired result.

Where I'm having trouble is how to have 10001 included and 10002 excluded. If I select all records where 2014-05-04 is BETWEEN starTDate AND endDate OR 2014-05-07 is BETWEEN startDate and endDate I end up with both rows for 10001 and 10002 returned. Can I compare these two rows in a single query so that I can see that there is a gap between the end and start dates of 10002 but there isn't one for 10001?

update I should have said I'm looking for a total count of available resources with this. And, as requested, here is a sqlfiddle http://sqlfiddle.com/#!2/7035b/2

adear11
  • 935
  • 6
  • 11
  • Consider providing proper DDLs (and/or an sqlfiddle) TOGETHER WITH THE DESIRED RESULT SET – Strawberry Apr 03 '14 at 18:00
  • doesn't seem to lend itself well to a single SELECT statement; for each resource you will need to loop through the available date ranges, consolidating overlapping or adjacent entries, to build a true picture of availability. One thought, if the granularity is date rather than time, and the constraints are right, you could check (in your example) resources that were available on 5-04 and 5-05 and 5-06 and 5-07 and each of those is a simple query... then join the results together. – RobP Apr 03 '14 at 18:17
  • Ares you able to record when the resources are _not_ available, instead of when they _are_ available? _I.e._ are they permanently available unless reserved? If so the problem will be simplified. – Terje D. Apr 03 '14 at 20:15

1 Answers1

0

Here's a single query that uses RobP's idea. Verified it to work for the example scenario.

WITH dates AS (
     SELECT CAST('2014-05-04' AS DATETIME) 'date'
     UNION ALL
     SELECT DATEADD(dd, 1, t.date) 
       FROM dates t
      WHERE DATEADD(dd, 1, t.date) <= '2014-05-07')
SELECT resourceid
FROM availability, dates d
WHERE d.date >= startdate and d.date <= enddate
GROUP BY resourceid
HAVING COUNT(*) >= DATEDIFF("d",'2014-05-04','2014-05-07')+1

Got the idea about iterating through dates from Generate a resultset of incrementing dates in TSQL

Community
  • 1
  • 1
GratefulDisciple
  • 674
  • 8
  • 18