""Shaun"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] >> "Shaun" <[EMAIL PROTECTED]> wrote on 08/31/2005 04:56:23 PM: >> >>> >>> <[EMAIL PROTECTED]> wrote in message >>> >> news:[EMAIL PROTECTED] >>> > "Shaun" <[EMAIL PROTECTED]> wrote on 08/31/2005 04:04:20 PM: >>> > >>> >> >>> >> <[EMAIL PROTECTED]> wrote in message >>> >> >>> > >> news:[EMAIL PROTECTED] >>> >> > "Shaun" <[EMAIL PROTECTED]> wrote on 08/31/2005 02:43:33 >> PM: >>> >> > >>> >> >> Hi, >>> >> >> >>> >> >> This query counts the number of entries in the Bookings table per >>> > month, >>> >> > is >>> >> >> it possible for this query to return a zero where there are no >>> > entries >>> >> > for a >>> >> >> particular month? >>> >> >> >>> >> >> SELECT COUNT(Booking_ID) AS "Num_Bookings" >>> >> >> FROM Bookings >>> >> >> WHERE User_ID = 1 >>> >> >> AND Work_Type_ID = 12 >>> >> >> AND DATE_FORMAT(Booking_Start_Date, "%m-%Y") >= "04-2005" >>> >> >> AND DATE_FORMAT(Booking_Start_Date, "%m-%Y") <= "06-2005" >>> >> >> AND Booking_Type = "Booking" >>> >> >> GROUP BY MONTH(Booking_Start_Date) >>> >> >> ORDER BY Booking_Start_Date >>> >> >> >>> >> >> Thanks for your help. >>> >> >> >>> >> > >>> >> > You cannot have missing month values in your query result unless >>> > provide >>> >> > values to fill the gaps with. The easiest way to do that is to >> create >>> > a >>> >> > simple lookup table. >>> >> > >>> >> > CREATE TABLE monthList ( >>> >> > id int, >>> >> > name varchar(18) not null >>> >> > ) >>> >> > >>> >> > INSERT monthList (id, name) values (1,'January'),(2,'February'),... >>> > fill >>> >> > in the rest...,(12,'December'); >>> >> > >>> >> > Now you can have something to show even if your Bookings data >> doesn't. >>> >> > Here is how you would use it in your sample query: >>> >> > >>> >> > SELECT m.name, COUNT(b.Booking_ID) AS "Num_Bookings" >>> >> > FROM monthList m >>> >> > LEFT JOIN Bookings b >>> >> > ON m.id = MONTH(b.Booking_Start_Date) >>> >> > WHERE b.User_ID = 1 >>> >> > AND b.Work_Type_ID = 12 >>> >> > AND b.Booking_Start_Date BETWEEN '2005-04-01' and >> '2005-06-30 >>> >> > 23:59:59' >>> >> > AND b.Booking_Type = "Booking" >>> >> > GROUP BY m.name >>> >> > ORDER BY m.id; >>> >> > >>> >> > Since I am comparing date values to date values, this should >> process >>> > much >>> >> > faster (especially if Booking_Start_Date is the leftmost column in >> any >>> >> > index). The short date format works for April 1 because any date >>> > constant >>> >> > without a time value is considered to be midnight (00:00:00). I had >> to >>> >> > include the last second of June 30 so that you would detect >> bookings >>> > that >>> >> > happened on that date (so you searched across the whole day and >> didn't >>> >> > just stop at midnight at the start of the day). An alternative to >>> > using >>> >> > the BETWEEN...AND... comparitor for date ranges is to look for >> "less >>> > than >>> >> > the next day". >>> >> > >>> >> > SELECT m.name, COUNT(b.Booking_ID) AS "Num_Bookings" >>> >> > FROM monthList m >>> >> > LEFT JOIN Bookings b >>> >> > ON m.id = MONTH(b.Booking_Start_Date) >>> >> > WHERE b.User_ID = 1 >>> >> > AND b.Work_Type_ID = 12 >>> >> > AND b.Booking_Start_Date >= '2005-04-01' >>> >> > AND b.Booking_Start_Date < '2005-07-01' >>> >> > AND b.Booking_Type = "Booking" >>> >> > GROUP BY m.name >>> >> > ORDER BY m.id; >>> >> > >>> >> > And, just as an example, here is how you would handle the case >> where >>> > you >>> >> > wanted a month-by-month report that crosses over from one year to >> the >>> >> > next. This should show how many bookings you had in each month for >> the >>> >> > twelve months starting with April 2004. >>> >> > >>> >> > 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) >>> >> > WHERE 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; >>> >> > >>> >> > (NOTE: I used a MySQL-specific shortcut by using column numbers in >> the >>> >> > GROUP BY and ORDER BY clauses. A more ANSI way of writing those >>> > clauses >>> >> > would have been: >>> >> > >>> >> > GROUP BY YEAR(b.Booking_Start_Date), m.name >>> >> > ORDER BY YEAR(b.Booking_Start_Date), m.id; >>> >> > >>> >> > I think the shortcut method is just a tad easier to read.) >>> >> > >>> >> > >>> >> > >>> >> > HTH! >>> >> > >>> >> > Shawn Green >>> >> > Database Administrator >>> >> > Unimin Corporation - Spruce Pine >>> >> >>> >> Hi Shawn, >>> >> >>> >> Thanks for your reply but the query doesnt seem to work for me... >>> >> >>> >> mysql> SELECT M.Month_Name, >>> >> -> COUNT(B.Booking_ID) AS "Num_Bookings" >>> >> -> FROM Months M >>> >> -> LEFT JOIN Bookings B >>> >> -> ON M.Month_ID = MONTH(B.Booking_Start_Date) >>> >> -> WHERE B.User_ID = 1799 >>> >> -> AND B.Booking_Start_Date >= '2005-01-01' >>> >> -> AND B.Booking_Start_Date < '2005-12-01' >>> >> -> AND B.Booking_Type = "Booking" >>> >> -> GROUP BY M.Month_Name >>> >> -> ORDER BY M.Month_ID; >>> >> +------------+--------------+ >>> >> | Month_Name | Num_Bookings | >>> >> +------------+--------------+ >>> >> | May | 3 | >>> >> | June | 22 | >>> >> | July | 12 | >>> >> | August | 15 | >>> >> | September | 13 | >>> >> | October | 18 | >>> >> | November | 6 | >>> >> +------------+--------------+ >>> >> 7 rows in set (0.04 sec) >>> >> >>> >> mysql> >>> >> >>> >> Any ideas why this might be happening? >>> >> >>> > >>> > It seems to be working just fine. You didn't get any December bookings >>> > because the latest possible booking occurs before '2005-12-01 >> 00:00:00' . >>> > If you want see December bookings, change your end date to >> '2006-01-01' >>> > >>> > If that wasn't it, can you explain WHY those numbers are wrong as I >> have >>> > only the slimmest clue what your data is supposed to look like. If >> those >>> > aren't the numbers you wanted, what numbers did you expect? Please, >>> > provide sample queries to demonstrate what you expected and I can help >> you >>> > figure out what we are missing. >>> > >>> > Shawn Green >>> > Database Administrator >>> > Unimin Corporation - Spruce Pine >>> >>> Hi Shawn, >>> >>> I understand the fact that December isn't there but what about January - >> >>> April? >>> >>> >> >> D'OH! ROFL! >> >> It's completely MY fault!! The date and other restrictions based on the >> Bookings table should have been part of the ON sub-clause of the LEFT >> JOIN >> (the word OUTER is optional with MySQL. LEFT JOIN is equivalent to LEFT >> OUTER JOIN). >> >> By leaving them in the WHERE clause, I made the dates a required part of >> the result when they should have applied to the optional portion of the >> data on the "right" side of the LEFT JOIN. >> >> 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
