Re: Active Clients by Period Report
Message from Tim Anderson tma@netspace.net.au
http-equiv="Content-Type">
I should have said "MySQL Workbench". If you don't have it
installed, you can run the SQL in the mysql tool.
e.g.
> mysql -u openvpms -p openvpms
-Tim
On 24/11/2011 2:44 PM, Glen Hastie wrote:
type="cite">
Forgive my ignorance but what is "MySQL browser"?
Glen
wrote:
Can you try running the following SQL in your MySQL
browser?
Its the SQL that the report executes, with the parameters
hardcoded.
SELECT date_format(a.activity_start_time, '%d/%m/%Y') as
date_formatted,
date_format(a.activity_start_time, '%b') as month_format,
date_format(a.activity_start_time, '%Y') as year_format,
COUNT(DISTINCT
customer.entity_id) as cust_cnt FROM `acts` a LEFT JOIN
`financial_acts` fa
ON (fa.`financial_act_id` = a.`act_id`) LEFT JOIN
`participations` pet_p ON
(pet_p.`act_id` = a.`act_id`AND pet_p.arch_short_name =
'participation.patient') LEFT JOIN `entities` pet ON
(pet.`entity_id` =
pet_p.`entity_id` AND pet.arch_short_name =
'party.patientpet') LEFT JOIN
entity_relationships er ON (er.target_id = pet.entity_id AND
er.arch_short_name = 'entityRelationship.patientOwner') LEFT
JOIN entities
customer ON (customer.entity_id = er.source_id AND
customer.arch_short_name =
'party.customerperson') WHERE a.activity_start_time >=
"2011-01-01" AND
a.activity_start_time <= date_add("2011-12-31", INTERVAL
"23:59:59"
HOUR_SECOND) AND a.arch_short_name LIKE
"act.customerAccount%item" ORDER BY
a.activity_start_time ASC
If it doesn't work, what version of MySQL are you running?
You can find this out by running:
SHOW VARIABLES LIKE "version";
in the MySQL browser.
-Tim
