What about having another table, populated through triggers on the
reservations table. The new reservation_dates table would, in your
example, contain:

ID RESERVATION_ID DATERESERVED
------------------------------
1 1 01/01/2006
2 1 02/01/2006
...
14 1 14/01/2006
15 1 15/01/2006
16 2 03/01/2006
17 2 04/01/2006
18 2 05/01/2006

Then your query would be a bit simpler:

Select C.DAY, count(*) as total
from CALENDAR C
left join RESERVATION_DATES RD on C.DAY = RD.DATERESERVED
where C.DAY between '01/01/2006' and '01/31/2006'

This does make the database bigger, but should perform far better than
your solution.

Set

--- In firebird-support@yahoogroups.com, "yartax1" wrote:
> Hi,
>
> In my database I got a table with information about reservations.
> Some fields are:
>
> * ID
> * DATEIN // Checkin date
> * DATEOUT // Checkout date
> ...
>
> Some data would be:
>
> ID DATEIN DATEOUT
> ------------------------------
> 1 01/01/2006 15/01/2006
> 2 03/01/2006 05/01/2006
> ...
>
> At this point no problem. Next I need to perform a report like this:
>
> DATE NUM.RESERV
> 01/01/2006 1
> 02/01/2006 1
> 03/01/2006 2
> 03/01/2006 2
> ...
>
> The only method I know to perform this query is creating a new table
> named CALENDAR with all days from 2006 to 2050 and then do the next
> query:
>
> Select day,count(id) as total
> from CALENDAR
> left join RESERVATIONS on CALENDAR.DAY BETWEEN DATEIN AND DATEOUT
> where DAY between '01/01/2006' and '01/31/2006'
>
> The problem is that query is not efficient at all. Other method
> would be creating a procedure and doing 31 queries, but still not
> efficient.
>
> Any other tips?
>
> Thanks.
> Julian.