You are right, this idea is a good aproaching.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

>
> 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.
>