SQL query to identify available hourly providers

Asked

Viewed 63 times

1

I have a table for service providers that informs the time they are not available.

id_prestador | id_pedido | hora_entrada        | hora_saida
1            | abc1      | 2017-08-10 10:00:00 | 2017-08-10 14:00:00   
2            | xpto2     | 2017-08-10 15:00:00 | 2017-08-10 18:00:00

I need to make a query to identify the providers that are available at the requested time, for example:

Time requested: the following: Check-in: 2017-08-10 08:00:00 Check-out: 2017-08-10 11:00:00

In this example he would bring the provider 2

Some light? Thank you very much!

Solved:

SELECT *
FROM fcs_prestadores_pedidos
WHERE hora_entrada not BETWEEN '2017-08-10 16:00:00' AND '2017-08-10 18:00:00'
AND hora_saida not between '2017-08-10 16:00:00' AND '2017-08-10 18:00:00'
mysql database datetime calculus

  • What have been your attempts?

  • @Leandromarzullo: this is a classic case in SQL: intersection/overlap of intervals. On the web there are articles about it; search for sql interval overlap or sql range overlap or similar terms.

2 answers

2

Use the operator between if you want to verify an intevalue of a date 

where hora_entrada between  @Data1 and @Data2

Or use the operator AND to check the interval between two fields of your table.

where (hora_entrada >=  2017-08-10 15:00:00 And  hora_saida <= 2017-08-10 18:00:00)

  • then, in fact it is the opposite, it has to bring who is not between the interval

  • 1

    I need to make a query to identify the providers that are available at the requested time, for example: that’s not what your question says

  • 1

    truth, vlw, I’ll change the question

1


Use the clause NOT EXISTS:

SELECT p.*
  FROM prestador p
 WHERE NOT EXISTS(SELECT 1
                    FROM prestadores_pedidos pp
                   WHERE pp.id_prestador = p.id
                     AND (pp.hora_entrada BETWEEN '2017-08-10 08:00:00' AND '2017-08-10 11:00:00'
                      OR pp.hora_saida BETWEEN '2017-08-10 08:00:00' AND '2017-08-10 11:00:00'))

Observing: I’m guessing the names of the tables and columns since you didn’t inform.

  • is a table only helpers_requests I changed the 2 tables by the table helpers_requests, but it brought the 2 results

  • @Leandromarzullo you do not have a table only with the providers?

  • yes, actually there are 2 tables, one of registration of the providers that is the providers (with id, name, email, etc.) and another one with the registration of the orders that is the providers

  • @Leandromarzullo I changed the query to match its structure

  • he brought the 2 results, but based on his idea, I did the following that worked: SELECT * FROM fcs_providers WHERE hora_input not BETWEEN '2017-08-10 16:00:00' AND '2017-08-10 18:00:00' AND hora_output not between '2017-08-10 16:00:00' AND '2017-08-10 18:00:00'

  • @Leandromarzullo think that if you use the service providers directly you will never be able to bring providers who never had orders

  • Putz, it is true. and another thing, if the order is within an unavailable time it also brings, for example: the provider A starts at 15:00 and ends at 19:00 and the request was at 16:00 to 17:00, there both the start time and end time of the provider are not among the selected time, understood?

Show 2 more comments

Browser other questions tagged mysql database datetime calculus

You are not signed in. Login or sign up in order to post.