From: Peter Brawley
Date: May 17 2012 6:34pm
Subject: Re: Query help,,,
List-Archive: http://lists.mysql.com/mysql/227452
Message-Id: <4FB5449A.9030500@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>