Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
Getting daily count across a date range. |
Mon, Nov 23 2009 8:44 PM | Permanent Link |
Phil Read | 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 |
Tue, Nov 24 2009 9:13 AM | Permanent Link |
"John Hay" | 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 |
Tue, Nov 24 2009 9:07 PM | Permanent Link |
Phil Read | 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! |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |