List:General Discussion« Previous MessageNext Message »
From:Don Wieland Date:May 17 2012 2:37pm
Subject:Query help,,,
View as plain text  
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
Thread
Query help,,,Don Wieland17 May
  • RE: Query help,,,Rick James17 May
  • Re: Query help,,,Peter Brawley17 May