"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
