Oracle SQL LOOP (between 2 dates) and Counting

Question

I'm stuck with a SQL Query.

I have this table:

[Reserve]

ID NUMBER
START_DATE, DATE
END_DATE, DATE

.......... (more cols)

I need to look if the next count is more than 0 for any of the dates between START_DATE and END_DATE (including both). If the count is more than 0, the query must stop inmediately and return "There is no location" as a string.

SELECT Count(*)
  FROM disponibilidad
  JOIN estadia ON disponibilidad.identificador_estadia = estadia.identificador
 WHERE estadia.identificador = 1
   AND disponibilidad.numero_reservas > 500

Stuart Ainsworth · Accepted Answer · 2009-10-14 02:00:23Z

2

If I understand you correctly, this should work:

SELECT 'There is no location'
FROM Reserve r
WHERE EXISTS (SELECT *
              FROM disponibilidad 
                     JOIN estadia 
                        ON disponibilidad.identificador_estadia = estadia.identificador 
              WHERE estadia.identificador = r.ID
                   AND disponibilidad.numero_reservas > 500 
                   AND disponibilidad.date BETWEEN r.StartDate AND r.EndDate)
AND r.ID = 1

Since you didn't specify the relation between the table and the tables involved in the query, I guessed. And I may have guessed wrong. Such is life. :)

answered Oct 14, 2009 at 2:00

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sheldon Over a year ago

I figure it out by what you answer... I count the times it appears using the BETWEEN function :D Thank you!

Collectives™ on Stack Overflow

Oracle SQL LOOP (between 2 dates) and Counting

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related