CODEWITHSUNDEEP
sqlms-access
Ants1060
Ants1060

Reputation: 186

MS ACCESS – Return a daily count of booked resources within a date range

Please note: this is not for an Access project as such, but a legacy application that uses an Access database for its back end.

Setup
Part of the application is a kind of Gantt chart, fixed to single day columns, where each row represents a single resource. Resources are booked out for a range of days and a booking is for a single resource, so they cannot overlap on a row. The range of dates that is in view is user selectable, open ended, and can be changed by various methods, including horizontal scrolling using mouse or keyboard.

Problem
I've been tasked with adding a row to the top of the chart to indicate overall resource usage for each day. Of course that's trivially easy to do by simply querying for each day in the range separately, but unfortunately that is proving to be an expensive process and therefore slows down horizontal scrolling a lot. So I'm looking for a way to do it more efficiently, hopefully with fewer database reads.

Here is a highly simplified example of the bookings table:

booking_ID |   start_Date |    end_Date |  resource_ID
----------- -------------- ------------- -------------
         1     2014-07-17    2014-07-20             21
         2     2014-08-24    2014-08-29              4
         3     2014-08-26    2014-09-02             21
         4     2014-08-28    2014-09-04             19

Ideally, I would like a single query that returns each day within the specified range, along with a count of how many bookings there are on those days. So querying the data above for 20 days from 2014-07-17 would produce this:

check_Date | resources_Used 
----------- ---------------
2014-07-17                1
2014-07-18                1
2014-07-19                1
2014-07-20                1
2014-07-21                0
2014-07-22                0
2014-07-23                0
2014-08-24                1
2014-08-25                1
2014-08-26                2
2014-08-27                2
2014-08-28                3
2014-08-29                3
2014-08-30                2
2014-08-31                2
2014-09-01                2
2014-09-02                2
2014-09-03                1
2014-09-04                1
2014-09-05                0

I can get a list of dates in the range by using a table of integers (starting at 0), with this:

SELECT CDATE('2014-07-17') + ID AS check_Date FROM Integers WHERE ID < 20

And I can get the count of resources used for a single day with something like this:

SELECT COUNT(*) AS resources_Used 
FROM booking 
WHERE start_Date <= CDATE('2014-09-04')
AND end_Date >= CDATE('2014-09-04')

But I can't figure out how (or if) I can tie them both together to get the desired results. Is this even possible?

Upvotes: 0

Views: 870

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13233

Create a table called "calendar" and put a list of dates into it covering the necessary timeframe. It just needs one column called check_date with one row for each date. Use Excel, start at whatever date and just drag down, then import into the new table.

After your calendar table is set up you can run the following:

select c.check_date, count(b.resource_id) as resources_used
  from calendar c, bookings b
 where c.check_date between b.start_date and b.end_date
group by c.check_date

Upvotes: 1

Related Questions