<[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > "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 > >
Hi Shawn, I am using 4.1.9. The system has been up and running for 3 years now so I am 'fairly' locked in but if your suggestion will make things easier in the future then its worth the effort. However we already keep unavailability in the bookings table so the we can identify time off i.e. holiday sickness etc. Bookings can never be made at the weekend and that is handled at the application layer although a booking could span a weekend - this could be rectified. Unavailability descriptions are help in a seperate table. The reason I started this was because I was asked to produce a report detailing the capacity worked by staff i.e. ( (days worked + days unavailable) / days available in month(i.e. working days) ) * 100 = capacity worked I have emailed a copy to you so you can see what I mean - hope you don't mind! Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
