<[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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
