Using a stored procedure is a nice way to avoid his calendar table,
but it suffers from the same problem as his original left join - no
indexes can be used (parameters cannot be indexed, and you're doing
<parameter> BETWEEN, not <field> BETWEEN). With a few reservations
this may not be a problem, but if he has lots of them (millions),
speed problems will only get worse as time go by.

Though maybe 'where datein <= :ret_date and dateout >= :ret_date'
could be worth a try. Hmm, maybe I was wrong in my assumption above -
the optimizer could in fact use indexes if doing this transformation
internally, but is it smart enough to do it in such a case?

Set

--- In firebird-support@yahoogroups.com, "Damir Tesanovic" wrote:
> Or maybe you could create above procedure (there are probably some
> syntax mistakes):
> create procedure (begin_date as date, end_date as date)
> returning (ret_date date, resnum integer)
> as
> ret_date= begin_date;
> while (begin_date <= end_date) do
> begin
> select count(id) from reservations
> where :ret_date between datein and dateout into :res
> suspend;
> ret_date = ret_date + 1;
> end
> end;
>
> I don't know how fast is this procedure because I didn't tryed it
> but with this aproach you don't have to create any extra tables.