select
CONVERT_TZ(cases_audit.date_created,'+00:00','+10:00') as 'date_closed',
accounts.name as 'account',
cases.case_number as 'case_id',
cases.name as 'case_name'
from
cases_audit
join cases on cases.id = cases_audit.parent_id
join accounts on accounts.id = cases.account_id
where
cases_audit.field_name = "status"
and cases_audit.after_value_string = "Closed"
order by
accounts.name asc,
cases_audit.date_created desc;
Using the cases.date_modified is not the same as the date a case is closed, as the case may have been modified after it was closed. This query will return the date the case was transitioned to 'Closed' provided the cases.status field is being audited.
Be the first to comment
You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.