"Shaun" <[EMAIL PROTECTED]> wrote on 09/01/2005 05:38:01 AM:
<big snip -- see earlier posts in this thread for details>
> >> The corrected query should read:
> >> SELECT m.name as month, YEAR(b.Booking_Start_Date) as year,
> >> COUNT(b.Booking_ID) AS "Num_Bookings"
> >> FROM monthList m
> >> LEFT JOIN Bookings b
> >> ON m.id = MONTH(b.Booking_Start_Date)
> >> AND b.User_ID = 1
> >> AND b.Work_Type_ID = 12
> >> AND b.Booking_Start_Date >= '2004-04-01'
> >> AND b.Booking_Start_Date < '2005-04-01'
> >> AND b.Booking_Type = "Booking"
> >> GROUP BY 1, 2
> >> ORDER BY 1, m.id;
> >>
> >> I am so very sorry! Modify my other examples in the same way and they
> >> should work too.
> >>
> >> Shawn Green
> >> Database Administrator
> >> Unimin Corporation - Spruce Pine
> >
> > Thanks Shawn :)
> >
>
> Hi Shawn,
>
> This is all working fine except where a booking spans more than one day
i.e.
> a holiday. I have a column called Booking_End_Date, is there a way to
count
> all the days used where a booking spans more than one day?
>
> Thanks for your help.
>
That's a different question, isn't it. Your first question asked to count
all of the bookings where the starting date was within a certain date
range. Your second question might be rephrased as:
Tell me how many days were booked within a certain date range.
See the difference between the two functions? One is a simple count of how
many rows are in the table, the other is the sum of bookable days. What
functions we can use to make that calculation easier depend on what
version MySQL you are running. What version MySQL are you running? Is this
a simple date span calculation or do we need a list of holidays so that if
a holiday occurs within the span covered by Booking_Start_Date to
Booking_End_Date it won't count as a booked day? Do you count weekends
too? Covering the cases of where a booking starts in one month and ends in
another can also get complicated (oh, it's do-able but it's not as simple
as you might think).
The more exceptions you want to add into this formula, the more convoluted
it becomes and the more processing required to reach the answer you want.
Plus, there is definitely more than one solution so if one way is too slow
or cumbersome or complex, try another.
One of the ways to simplify this calculation is to not enter bookings
across date spans but enter them into the table as a "booking" for each
day. That way you just don't enter a booking for holidays and weekends.
Since each booking has the same "customer_id" or "event_id" (or both) you
can tell instantly how many days were booked for any event or customer
within any month. In this suggestion, complex SQL wasn't the solution but
changing the way you store the data was. If you are locked into your
current storage plan, then you are left with complex SQL processing to get
at your data.
One way to do the multiple-date-entries-per-booking method is to create a
new table, `bookingdates`, that stores the id of the booking and the date
for the booking. Bookings that span multiple dates would have multiple
entries.
CREATE TABLE bookingdates (
booking_id int not null
, bookingdate datetime not null
, UNIQUE (booking_ID, bookingdate)
, key(bookingdate, booking_ID)
);
The indexes are called "covering indexes" and will basically allow you to
load the entire table into memory twice. Once sorted by booking_ID, the
other sorted by bookingdate. Because the index contains all of the data
you could want from a query, the datafile will not need to be touched to
do the actual retrieval. One less random seek and read on the disk means
much faster performance to your application. Just make sure that you do
not create entries where bookings do not happen (like holidays and
weekends). This table, properly filled in, makes not just your first query
but also your second query trivial to answer.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
