SQL query to identify providers available on time

Question

SQL query to identify providers available on time

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I have a table for service providers that informs the schedule that 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 an appointment to identify the providers that are available at the requested time, for example:

Timetable requested: Entry: 2017-08-10 08:00:00 Checkout: 2017-08-10 11:00:00

In this example it would bring the provider 2

Any light? Thank you!

Resolved:

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 datetime

asked by anonymous 03.08.2017 / 14:45

2 answers

2

Use the operator if you want to check an integer of a date

where hora_entrada between  @Data1 and @Data2

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

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

03.08.2017 / 14:52

Query in column type date with filter of the current week JavaScript does not execute

score 1 · Accepted Answer

Use the NOT EXISTS clause:

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'))

Note: I'm assuming the names of tables and columns since you did not report them.