MySQL Forums
Forum List  »  Oracle

Help with heavy query
Posted by: Hector Tino
Date: October 23, 2014 02:39PM

Hi !
I have a big problem with a fairly heavy query that gives me the availability of a hospital to make an appointment from the internet.
By itself takes a second for a specific date , the problem comes when I have to call 365 times to get the availability of a whole year , day by day . doctors showing voids of the year (this is a requirement ) .

I tried putting it in a loop that goes by queries like the one below and even creating a huge consultation with several joining together but it takes much and gives the feeling that has been hanging the system. anyone could give me some trick to improve the query ? is as follows :

SELECT count(habitaciones.tipoHabitacion) as total
FROM habitaciones
WHERE habitaciones.idhospital= '1' AND habitaciones.tipoHabitacion = '127'
AND = '1'
AND habitaciones.idHabitacion NOT IN
(SELECT bloqueoHabitaciones.idHabitacion
FROM bloqueoHabitaciones WHERE bloqueoHabitaciones.idhospital = '1'
AND NOT (DATE(bloqueoHabitaciones.fechaInicial) >= DATE('" + fechaFin + "')
OR DATE(bloqueoHabitaciones.fechaFinal) <= DATE('" + fechaInicio + "')))
AND habitaciones.idHabitacion NOT IN
(SELECT reservas.idHabitacion FROM reservas WHERE reservas.idhospital = '1'
AND reservas.estado !='Eliminada' AND reservas.estado !='No Show' AND reservas.estado !='Reserva cancelada'
AND not (DATE(reservas.fechaEntrada) >= DATE('" + fechaFin + "')
OR DATE(reservas.fechaSalida) <= DATE('" + fechaInicio + "')))

Thank you very much in advance for your help!!

Options: ReplyQuote

Written By
Help with heavy query
October 23, 2014 02:39PM
October 23, 2014 06:40PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.