Hi Tim & Team

This is quite simple in theory and I was going to use a simply query to
get a count of reservations for a particular day in a for next loop, but
I know there's probably a far more efficient way in SQL.

In a nutshell I have a table of bookings, each record stores a roomtype
and a from and to date for the booking.

ID   RoomType    FromDate    ToDate      Guest
----------------------------------------------
1    DOUBLE      20/11/09    24/11/09    John
2    SINGLE      22/11/09    23/11/09    Paul
3    DOUBLE      18/11/09    22/11/09    George
4    SINGLE      29/11/09    30/11/09    Ringo


OK now I'm trying to get a result set that will display a count of
bookings grouped by roomtype for each day of the month (or selected date
range)

something like this...


RoomType    Date       Count
----------------------------
SINGLE      18/11/09   0
DOUBLE      18/11/09   1
SINGLE      19/11/09   0
DOUBLE      19/11/09   1
SINGLE      20/11/09   0
DOUBLE      20/11/09   2
SINGLE      21/11/09   0
DOUBLE      21/11/09   2
SINGLE      22/11/09   1
DOUBLE      22/11/09   1

and so on.

Thanks

Phil

> ID   RoomType    FromDate    ToDate      Guest
> ----------------------------------------------
> 1    DOUBLE      20/11/09    24/11/09    John
> 2    SINGLE      22/11/09    23/11/09    Paul
> 3    DOUBLE      18/11/09    22/11/09    George
> 4    SINGLE      29/11/09    30/11/09    Ringo
>
>
> OK now I'm trying to get a result set that will display a count of
> bookings grouped by roomtype for each day of the month (or selected date
> range)

It can be done in DBISAM using SQL with the help of an integers table.  If
you create a table with one field named i and populate it with as many days
as you are goung to want to query (eg from 0 to 365 for a year).  Presuming
the Todate is a departure date (not to be included in count) then the
following should give you your list without zeros for roomtypes with no
bookings.

SELECT CAST('2009-01-01' AS DATE) + i AS BookingDate,Roomtype,COUNT(id) AS
BOOKED FROM Integers
LEFT OUTER JOIN Bookings ON
CAST('2009-01-01' AS DATE) + Integers.i >= Bookings.FromDate AND
CAST('2009-01-01' AS DATE) + Integers.i < Bookings.ToDate
where i between 0 and CAST('2009-01-31' AS DATE)-CAST('2009-01-01' AS DATE)
GROUP BY BookingDate,Roomtype
ORDER BY BookingDate,Roomtype;


If you need zeros for roomtypes with no booking you will need a roomtypes
table and a script like the following should do what you asked for.

SELECT CAST('2009-01-01' AS DATE) + i AS Bookingdate,Roomtype,0 AS Booked
into memory\temp FROM
Integers,RoomTypes
WHERE i BETWEEN 0 and CAST('2009-01-31' AS DATE)-CAST('2009-01-01' AS DATE)
UNION
SELECT CAST('2009-01-01' AS DATE) + i AS BookingDate,Roomtype,COUNT(id) AS
BOOKED FROM Integers
LEFT OUTER JOIN Bookings ON
CAST('2009-01-01' AS DATE) + Integers.i >= Bookings.FromDate AND
CAST('2009-01-01' AS DATE) + Integers.i < Bookings.ToDate
where i between 0 and CAST('2009-01-31' AS DATE)-CAST('2009-01-01' AS DATE)
GROUP BY BookingDate,Roomtype;

SELECT BookingDate,Roomtype,MAX(Booked) as Booked FROM memory\temp
GROUP BY BookingDate,Roomtype


John

John Hay wrote:
> Phil
>
>> ID   RoomType    FromDate    ToDate      Guest
>> ----------------------------------------------
>> 1    DOUBLE      20/11/09    24/11/09    John
>> 2    SINGLE      22/11/09    23/11/09    Paul
>> 3    DOUBLE      18/11/09    22/11/09    George
>> 4    SINGLE      29/11/09    30/11/09    Ringo
>>
>>
>> OK now I'm trying to get a result set that will display a count of
>> bookings grouped by roomtype for each day of the month (or selected date
>> range)
>
> It can be done in DBISAM using SQL with the help of an integers table.  If
> you create a table with one field named i and populate it with as many days
> as you are goung to want to query (eg from 0 to 365 for a year).  Presuming
> the Todate is a departure date (not to be included in count) then the
> following should give you your list without zeros for roomtypes with no
> bookings.
>
> SELECT CAST('2009-01-01' AS DATE) + i AS BookingDate,Roomtype,COUNT(id) AS
> BOOKED FROM Integers
> LEFT OUTER JOIN Bookings ON
> CAST('2009-01-01' AS DATE) + Integers.i >= Bookings.FromDate AND
> CAST('2009-01-01' AS DATE) + Integers.i < Bookings.ToDate
> where i between 0 and CAST('2009-01-31' AS DATE)-CAST('2009-01-01' AS DATE)
> GROUP BY BookingDate,Roomtype
> ORDER BY BookingDate,Roomtype;
>
>
> If you need zeros for roomtypes with no booking you will need a roomtypes
> table and a script like the following should do what you asked for.
>
> SELECT CAST('2009-01-01' AS DATE) + i AS Bookingdate,Roomtype,0 AS Booked
> into memory\temp FROM
> Integers,RoomTypes
> WHERE i BETWEEN 0 and CAST('2009-01-31' AS DATE)-CAST('2009-01-01' AS DATE)
> UNION
> SELECT CAST('2009-01-01' AS DATE) + i AS BookingDate,Roomtype,COUNT(id) AS
> BOOKED FROM Integers
> LEFT OUTER JOIN Bookings ON
> CAST('2009-01-01' AS DATE) + Integers.i >= Bookings.FromDate AND
> CAST('2009-01-01' AS DATE) + Integers.i < Bookings.ToDate
> where i between 0 and CAST('2009-01-31' AS DATE)-CAST('2009-01-01' AS DATE)
> GROUP BY BookingDate,Roomtype;
>
> SELECT BookingDate,Roomtype,MAX(Booked) as Booked FROM memory\temp
> GROUP BY BookingDate,Roomtype
>
>
> John
>
>

That's fantastic John! Worked a treat, thanks sooo much! really Cheers!