Calculation of total equipment for a date range

Question

Project: I am working on a project that relates to some rooms and the equipment used in the rooms. The software is intended for planning equipment in rooms. In other words, it is backup software that backs up selected equipment in separate rooms for the required dates and time intervals. I have many tables in the MYsSQL database that work with Php, but I will talk about the tables in question. In the tables, I will talk about the equipment table (table A), the schedule table (table B), and the equipment used in the corresponding schedule (table C).

Table A: Equipment List Table

eqid | eqName | available| 1 | book | 90 | 2 | pen | 82 | 3 | computer | 25 | 

In table A; eqid is a unique identifier for equipment, eqName is the name of the equipment available is a common existing equipment.

Table B: Schedule Table

 scheduleid | startDate | endDate | startTime | endTime | office | 1 | 2012-08-27 | 2012-08-27 | 08:30:00 | 10:00:00 | room1 | 2 | 2012-08-27 | 2012-08-27 | 09:30:00 | 11:00:00 | room3 | 3 | 2012-08-28 | 2012-08-30 | 08:30:00 | 12:00:00 | room2 | 4 | 2012-08-29 | 2012-08-31 | 11:30:00 | 14:00:00 | room1 | 5 | 2012-08-28 | 2012-08-28 | 10:30:00 | 14:00:00 | room3 | 6 | 2012-08-27 | 2012-08-30 | 08:30:00 | 10:00:00 | room4 | 7 | 2012-08-27 | 2012-08-27 | 10:30:00 | 12:00:00 | room4 | 8 | 2012-08-27 | 2012-08-30 | 08:30:00 | 11:00:00 | room6 | 9 | 2012-08-27 | 2012-08-27 | 10:30:00 | 12:00:00 | room5 | 

In table B; schedid is a unique identifier for a schedule, startDate and endDate are a range of dates for a schedule, start and end times for a schedule, office means that in the case of a schedule. Let me give you an example here. Scheduleid 1 means that the reservation is made on August 27, 2012, on Monday, and from 08:30 to 10:00. Since it starts and ends on the same day, this is just a one-day room reservation1. However, Scheduleid 3 means that the reservation starts on August 28, 2012, Tuesday and lasts until August 30, 2012, Thursday at 08:30 - 12:00 ... in other words, it lasts 3 days and every day from C 08:30 to 12:00 ... So, there is a reservation from Tuesday to Thursday from 08:30 to 12:00 in room 2 ... I hope this is clear.

Table C: equipment using the appropriate schedule

 Autoid | scheduleid | eqid | amountInSch| 1 | 1 | 1 | 2 | 2 | 1 | 2 | 3 | 3 | 1 | 3 | 1 | 4 | 2 | 1 | 1 | 5 | 2 | 2 | 1 | 6 | 2 | 3 | 2 | 7 | 3 | 2 | 1 | 8 | 3 | 3 | 3 | 9 | 4 | 2 | 1 | 10 | 4 | 3 | 1 | 11 | 5 | 1 | 1 | 12 | 6 | 1 | 1 | 13 | 6 | 3 | 2 | 14 | 6 | 2 | 4 | 15 | 7 | 1 | 5 | 16 | 7 | 2 | 6 | 17 | 8 | 2 | 1 | 18 | 9 | 1 | 8 | 19 | 9 | 2 | 5 | 20 | 9 | 3 | 6 | 

In table C: Autoid is a unique automatic identifier generated using auto-increment, schedid is from table B, eqid is from table A, amountInSch represents how much (quantity) of equipment will be used in the corresponding schedule. I want to give an example here. Table 1 in table C contains 3 rows. This means that in table 1, associated with table 1, associated with TAble B, two books (eqid 1), 3 pens (eqid 2) and 1 computer (eqid 3) in room 1, the indicated dates and times in table B will be used Another example is that schedid 3 in table C refers to two rows. This means that 1 pen (eqId 2) and 3 computers (eqId 3) will be used in room 2 from August 27 to 30, 2012 every day from 08:30 to 12:00.

The above explanation and give some information about the project. Table rows are not constant. When you make a reservation, there will be a new row in table B, and if it is selected, new rows will appear in table C ...

Question:

I want to calculate the left amount of certain equipment when I put eqId, startDate, endDate, startTime and endTime ...

Example:

eqId: 1 (book)

startDate: 2012-08-27

endDate: 2012-08-27

startTime: 08:30:00

endTime: 12:00:00

The result should be: 14 books used in the schedule, and 76 remaining books available

Because: if you look at schedIds and its associated eqIds, you will only see 1, 2, 6, 7, 9 scheduleIds related to my request (dates and eqId). If you sum the entire amount associated with table C, you will get the wrong result. In other words, the corresponding amounts for eqId (1-book) and for 1, 2, 6, 7, 9 scheduleIds are 2, 1, 1, 5, 8, respectively. Therefore, if you sum them up, you will get 17, which is wrong. Since schedules 1 and 9 do not intersect with each other in terms of start and end times, and 6 and 7 do not intersect with each other. as a result, they remain single and can be counted separately. We should consider 1 and 9 as summed 8, because 8 is greater than 2. This is the same for 6 and 7, considered 5 because of 5 more than 1 ...

So people! I am not sure how this can be summed up in a programming algorithm. Is there a way to do it in SQL or do I need to use PHP and Mysql together? And How?

Hurrah!

SQLFiddle Entries

Answer 1

I started with the following SQL to collect all date ranges that intersect with a given range:

 SELECT MAX(available) - IFNULL(SUM(amountInSch), 0) FROM Table1 LEFT JOIN Table3 USING (eqid) LEFT JOIN Table2 USING (scheduleid) WHERE DATE(startDate) <= '2012-08-27' AND DATE(endDate) >= '2012-08-27' AND endTime > '08:30' AND startTime < '12:00' AND eqid = 1 

Fiddle

This is only the first part. Then you have to decide the possible matches; it would be impractical to do with SQL, so I would suggest doing it in PHP.

The general algorithm that I would choose, unfortunately, is O (n ** 2), as follows:

• create a timeline (demarcated every day) with time in the form of a horizontal axis
• iterate over each date / time range and mark the time of its left and right edges to create time segments of all possible permutations.
• using segments, you sum vertically for the floors, and you take the maximum daily maximum.

Hope this helps.