Hi,
I'm trying to get statistics from a variety of related tables where the statistical counts are based on comparing the value on one table with an equivalent value on a second table (and I want to view the results grouped by time period, day, week, month, etc); e.g.
Consider two tables - Customer & Customer_Action which are related as a one-to-many (one customer can take many actions):
Customer: id, create_date, customer_name, etc.
Customer_Action: id, customer_id, create_date, action, etc.
If the customer took their first action at the same time as they registered on the system (i.e. if the customer record was created at the same time as the very first action record)
YES_count is incremented by 1 else
NO_count is incremented by 1
So running the query against the database the report would look something like:
Customers Yes No
January 8 5 3
February 14 9 5 .... Etc.
I've tried this around a number of different ways but always seem to end up with double counting in one way or another: see this sample data
Customer Create_Date Action_Date
01 05/07/2008 12:36 05/07/2008 12:36
01 05/07/2008 12:36 28/08/2008 22:22
02 10/07/2008 12:04 10/07/2008 12:04
03 10/07/2008 12:12 10/07/2008 12:12
This should give me
Count Yes No
July 3 3 0
...... but I get always get a customer counts of 4 2 2!
My current statement is .....
SELECT count( m2u_Customer.id ) AS Customer,
min( m2u_Customer_Action.action_date ) AS 'Action_Date',
DATE_FORMAT( m2u_Customer.create_date, '%m-%M' ) AS Month,
sum(case when m2u_Customer.create_date = m2u_Customer_Action.create_date then 1 else 0 end) as Yes,
sum(case when m2u_Customer.create_date != m2u_Customer_Action.create_date then 1 else 0 end) as No
FROM m2u_Customer
LEFT JOIN m2u_Customer_Action ON ( m2u_Customer.id = m2u_CustomerAction.customer_ id )
WHERE m2u_Customer.create_date > '2008-07-02'
AND m2u_Customer.create_date < '2008-08-01'
GROUP BY DATE_FORMAT( m2u_Customer.create_date, '%m-%M' )
Can anybody help me resolve this conundrum?
Regards.
Patrick
