"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
