0

I'm looking for a function for my specific problem.

I have a system rent PC. Every PC model is available in a certain quantity, for example ACER XY = 7 units. TOSHIBA ZZ = 5 units...

User can select a range date (with time) and a (or more than 1) PC MODEL for the rent in this range.

For example from 2017-12-01 T 13:10.....to.......2017-12-05 T 21:00....with ACER XY (2 units for rental)

My function as to search inside database if in this range there are some rental entry with this PC MODEL and return true or false for submitting form. (In this example so is accepted up to 5 entries...cause 7 total units minus 2 to rent = 5).

I don't know how to approach.

For example if in database i've already 2 sequentially events + 1 overlap event like:

2017-12-02 T 13:10 to  2017-12-05 T 21:00 with ACER XY quantity 1 customer AA
2017-12-05 T 21:00 to  2017-12-07 T 11:00 with ACER XY quantity 3 customer BB
2017-12-07 T 09:00 to  2017-12-09 T 08:00 with ACER XY quantity 3 customer CC

..................

If i try to insert 2017-12-01 T 13:10.....to.......2017-12-05 T 21:00....with ACER XY (2 units for rental) I need to return TRUE

If i try with 2017-12-01 T 13:10.....to.......2017-12-05 T 21:10....with ACER XY (5 units for rental) I need to return FALSE

If i try with 2017-12-01 T 13:10.....to.......2017-12-07 T 08:59....with ACER XY (2 units for rental) I need to return TRUE

If i try with 2017-12-01 T 13:10.....to.......2017-12-07 T 09:01....with ACER XY (2 units for rental) I need to return FALSE

I hope the example are good to understand?

My tables (simplified):

Table RentalTimeline:
ID (INT), start (DATETIME), end (DATETIME), pc_id (INT), quantityForRent (INT), customer_ID (INT)

Table PC:
ID (INT), ModelName (varchar), QuantityAvailableTotal (INT)

UPDATE: @Juan Carlos Oropeza solution i think doesn't work cause if I this situation: 7 TOTAL PC ACER AVAILABLE

and these 3 events already in DB (i remove times to simplify):

2017-12-02 to  2017-12-15 with ACER quantity 1 customer AA
2017-12-05 to  2017-12-06 with ACER quantity 3 customer BB
2017-12-07 to  2017-12-09 with ACER quantity 3 customer CC

and I try to insert an event:

2017-12-01 to  2017-12-20 with ACER quantity 1

With @Juan Carlos Oropeza solution I'll get FALSE cause with my future new entry I'll cover all 3 events already inserted...and the SUM is equal to 7 (my max PC available), but 2 events are sequentially then i need to return TRUE in this case (cause from 12-01 to 12-01 I'll have 1 PC (the new inserted), from 12-02 to 12-04 I'll have 2 PC (1+1new), from 12-05 to 12-06 I'll have 5 pc (1+3+1new), from 12-07 to 12-09 I'll have 5 pc (1+3+1new), from 12-10 to 12-15 I'll have 2 pc (1+1new) and from 12-16 to 12-20 I'll have 1 pc (1new))

Giuseppe Lodi Rizzini
  • 1,045
  • 11
  • 33
itajackass
  • 346
  • 1
  • 2
  • 15
  • Your new example doesnt match unless you mean your `end_date` are open range. Because the dates collide on `2017/12/07` see [**here**](https://i.stack.imgur.com/ZVnZV.png) – Juan Carlos Oropeza Nov 07 '17 at 14:25
  • You're right sorry...deleting times to simplify example i haven't see this error. I've corrected my example. – itajackass Nov 07 '17 at 15:34
  • Dont be sorry I was checking and found a case where my query doesnt work. https://i.stack.imgur.com/CI8aK.png – Juan Carlos Oropeza Nov 07 '17 at 15:36
  • Maybe I need a function to search for events with "pc_id" needed and create a sub-ranges of end and start... and for every sub-range, count the quantity occuped. but i don't know the logic to create the sub-ranges with small codes...is my idea right? – itajackass Nov 07 '17 at 15:47
  • Nope, you need to create a date list for all the days. I'm working on it – Juan Carlos Oropeza Nov 07 '17 at 15:48
  • ok. but i've semplified my example removing times. We need to consider times also. like 2017-12-02 T 13:10 to 2017-12-05 T 21:00... in this case, i don't know ho to create a entire list of combinations... – itajackass Nov 07 '17 at 16:02
  • wow. I just finish do it with just date. check here. http://sqlfiddle.com/#!9/65f13a/5 – Juan Carlos Oropeza Nov 07 '17 at 16:05
  • Do you really rent pc by the hour? cant simplify to dates? – Juan Carlos Oropeza Nov 07 '17 at 16:06
  • Hi thanks for the example i'll try now. BTW I've created an example based on PC to simplify...my system, at real, need to rent industrial machinery so time is money :) – itajackass Nov 07 '17 at 16:14
  • I see. Sorry but dont see a way to split the ranges together – Juan Carlos Oropeza Nov 07 '17 at 16:18
  • I forgot to tell i can use a PHP function, not only a mysql query to do my goal...so i'll can create a complex php function. My idea is to create an array in php with all start and end (with times also) where is an intersection....order the array and every 2 entries ( like array[0] and array[1] ) use your first solution to count, insert the count in a second array2 and loop for another check with array[1] and array[2].....insert the sum into array2...and so on....at the end check if in array2 there's a number who, added to my qty to insert, exceed my max available PCs.... – itajackass Nov 07 '17 at 16:28

1 Answers1

0

I found this function

CREATE PROCEDURE make_intervals(startdate timestamp, 
                                enddate timestamp, 
                                intval integer, 
                                unitval varchar(10))

So for your example you will call:

call make_intervals('2017-12-02 00:00:00','2017-12-07 00:00:00', 1,'HOUR');

To create a table time_intervals with 1 hour intervals. For that date range create 120 intervals. If you make the interval MINUTE will create 7200 intervals.

So to check if you already have used too many PC in that range your query should be:

SQL DEMO

SELECT t.interval_start, 
       t.interval_end,
       SUM(qty)
FROM RentalTimeline R
JOIN time_intervals T
  ON t.interval_start <= r.end
 AND t.interval_end >= r.start
GROUP BY t.interval_start, 
         t.interval_end
HAVING SUM(qty) > 6  
               -- 6 is Number of PC available - Number of PC request for new customer

You also need to join with your PC table to filter the ranges related to the model requested by customer.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118