check the available time periods in the SQL table

Question

check the available time periods in the SQL table

Navigation

#1 by (1 votes)

1

I'm developing a scheduling schedule for a software that manages schedules available for scheduling, it seems odd that I post this here more by incredible than it seems I did not find anything of the genre here on the web, well my scenario is as follows: p>

I have a table with the following structure:

CREATE TABLE cad_compromisso
(
  id serial NOT NULL,
  data_entrada date,
  nome_func character varying(50),
  servico character varying(100),
  horaini time without time zone,
  horafin time without time zone,
  nome_cli character varying(100)
)

where in the same I save the date in which the period that the customer will be serviced, which in the case are the fields "horaini" and "horafin", so far so good, now the problem is that, whenever the attendant tries to create a new appointment, it is necessary to check if there is already an appointment marked within the informed period. So far I have not been able to leave 100%,

The maximum I got was this:

select * from cad_compromisso
   where horafin <= '" & cmbhorafinal.Text & "'
   and horaini >= '" & cmbhorario.Text & "'
   or data_entrada = '" & calendario.Value & "'
   and nome_func = '" & cmbfuncionario.Text & "'
   and horafin > '" & cmbhorario.Text & "'
   and horaini < '" & cmbhorafinal.Text & "'
order by horaini "

If you can send a light, I would appreciate it ...

sql query

asked by anonymous 14.04.2015 / 06:11

1 answer

C # and mysql pagination Organization of directories in the Silex framework

score 1 · Answer 1

You can simply try:

select *
from cad_compromisso
where data_entrada = '" & calendario.Value & "'
  and nome_func = '" & cmbfuncionario.Text & "'
  and (    '" & cmbhorario.Text & "'   between horaini and horafin 
        or '" & cmbhorafinal.Text & "' between horaini and horafin
        or ( horaini > '" & cmbhorario.Text & "' and horafin < '" & cmbhorafinal.Text & "')
  )

The logic behind the statement is this: I believe there is already a conflict with another appointment when the date is the same , the employee is the same and there is a time overlap .