Hi All,
I am trying to output a report from a query. The report basically
needs to list for each month the number of people seen per doctor.
e.g
Client Jan, Feb, Mar, April, May Jun, Jul, Aug, Sept, Oct, Nov, Dec
Doctor A- 0, 0, 12, 40, 0, 12, 50,20, 25, 0,
5, 13
Doctor B- 0, 23, 12, 40, 0, 12, 50, 35, 25,
0, 5, 13
Doctor C ect...
The problem is I am not sure how to go about outputting this as per
the format of the above example.
The query which works looks something like this (mysql):
SELECT users.userID, users.userFirstName, users.userSurname,
reviewing_doctors.reviewing_doctor_id, MONTH(dateSent) AS ReportMonth,
YEAR(dateSent) AS YEAR,
MONTHNAME(dateSent) AS month_name, COUNT(*) AS total FROM doctor_review
INNER JOIN reviewing_doctors
ON doctor_review.reviewing_doctor = reviewing_doctors.reviewing_doctor_id
INNER JOIN users
ON reviewing_doctors.reviewing_doctor_UserID = 1
WHERE dateReceived > '2004-09-12'
AND dateReceived < '2007-09-12'
GROUP BY ReportMonth, reviewing_doctor
ORDER BY users.userFirstName;
Any help/ideas will be much appreciated as I have not got very far on this one.
Thanks,
Mike.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Get the answers you are looking for on the ColdFusion Labs
Forum direct from active programmers and developers.
http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284616
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
