On 2012-05-17 9:37 AM, Don Wieland wrote:
Hi folks,
I am trying to compile a query that does statistics on appointments
based on specific criteria. Here is my starting query:
SELECT
u.user_id,
c.client_id,
c.first_name,
c.last_name,
a.time_start AS stime,
FROM_UNIXTIME(a.time_start,'%Y-%m-%d') AS formatted
FROM tl_appt a
LEFT JOIN tl_users u ON a.user_id = u.user_id
LEFT JOIN tl_clients c ON a.client_id = c.client_id
LEFT JOIN tl_rooms r on a.room_id = r.room_id
WHERE a.appt_id IS NOT NULL AND
FROM_UNIXTIME(a.time_start,'%Y-%m-%d') between '2011-05-01' and
'2011-12-31' and r.location_id = '2' and a.user_id IN (14) ORDER BY
u.last_name, u.first_name, c.last_name, c.first_name
This will return a set of rows where a client may have MORE THEN ONE
appointment. From this set I need to narrow more:
1) Only display the first appointment PER Client. (there will be no
duplicate client_id)
Then once I have that set of rows established, I need to query for two
more result:
1) Show New Customers = those where the COUNT of appointments (in the
entire tl_appt table) LESS THAN the stime = 0
2) Show FORMER Customers = those where the COUNT of appointments (in
the entire tl_appt table) LESS THAN the stime > 0
I am sure I need a multiple select query, but I am having a hard time
wrapping my head around it.
Thanks for any feedback.
Conceptually the hard bit might be narrowing to the first appt per
client. For various approaches to this task see "Within-group
aggregates" at http://www.artfulsoftware.com/infotree/queries.php.
If new & former clients are to be retrieved from first appts only, you
might want to save the result of the within-groups agggregate query to a
result table and query that. If the whole process has to happen often,
consider developing a wee cube, or just a denormalised reslt table that
can be invoked whenever needed.
PB
-----------------
Don
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
