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.
Don
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
May 17
Rick James RE: Query help,,,
May 17, 2012; 08:45
Rick James
RE: Query help,,,
May 17
Peter Brawley Re: Query help,,,
May 17, 2012; 13:34
Peter Brawley
Re: Query help,,,
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!