Query help,,,
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. Don -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Query help,,,
For (1), ponder: Group-by trick example: Find the most populous city in each state: SELECT state, city, population, COUNT(*) AS num_cities FROM ( SELECT state, city, population FROM us ORDER BY state, population DESC ) p GROUP BY state ORDER BY state; +---+-+++ | state | city| population | num_cities | +---+-+++ | AK| Anchorage | 276263 | 16 | | AL| Birmingham | 231621 | 58 | | AR| Little Rock | 184217 | 40 | | AZ| Phoenix |1428509 | 51 | | CA| Los Angeles |3877129 |447 | ... -Original Message- From: Don Wieland [mailto:d...@pointmade.net] Sent: Thursday, May 17, 2012 7:37 AM To: mysql@lists.mysql.com Subject: Query help,,, 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. Don -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query help,,,
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
ATT: List OWNER/MODERATOR
List is broken, in many ways. Amongst many of its failures, the two current biggest is: 1/ This lists bounce messages are clueless they do not include the reject reason 2/ mysql-owner address STILL points to a sun address which of course is rejected as they no longer host this list. *sigh*
Re: ATT: List OWNER/MODERATOR
Am 18.05.2012 02:51, schrieb Noel Butler: List is broken, in many ways. Amongst many of its failures, the two current biggest is: 1/ This lists bounce messages are clueless they do not include the reject reason which bounce-messages are you meaning? may it be you mean the stupid auto-replies caused by a poor list-server missing the Precedence: bulk or Precedence: list headers? yes, this is really the only mailig-list out there causing vacation-replies if the rcpt MTA is working correct and no mailadmin can do anything :-( signature.asc Description: OpenPGP digital signature
Re: ATT: List OWNER/MODERATOR
On Fri, 2012-05-18 at 03:40 +0200, Reindl Harald wrote: Am 18.05.2012 02:51, schrieb Noel Butler: List is broken, in many ways. Amongst many of its failures, the two current biggest is: 1/ This lists bounce messages are clueless they do not include the reject reason which bounce-messages are you meaning? may it be you mean the stupid auto-replies caused by a poor list-server missing the Precedence: bulk or Precedence: list headers? Nope, however I have brought that up before, I'm not going to blacklist those senders since its likely through no fault of their own.. I'm talking about the messages that oracle were unable to deliver to us, quoting the msd ID's and the non-existant reason in their bounce header example, which is now pointless as it does not include the MTA error code information. I dare say its oracles screwup, since this list seems to be have been moved from sun to oracle and oracle just gone lets test, well ok, we can send a msg through, it goes out to users it must works and thats it, totally forgotten about, theres a reason why many dont use ezmlm, its evil in this day and age so many far better products about. yes, this is really the only mailig-list out there causing vacation-replies if the rcpt MTA is working correct and no mailadmin can do anything :-( maybe they can, if we can find one LOL
