Useful SQL database queries
Jump to navigation
Jump to search
A collection of useful database queries
All active translation activities for a certain locale (in this example de-DE)
Select name,id from job where id in (select job_id from workflow where target_locale_id = 25 and state = 'DISPATCHED' and iflow_instance_id in (select workflow_id from task_info where name like '%Translation%' and (state = 'ACTIVE' or state='ACCEPTED')))
Average job size (word count) by target locale
Select locale.iso_lang_code, locale.iso_country_code, AVG(total_word_count) from locale, workflow where workflow.state != "cancelled" and locale.id = workflow.target_locale_id GROUP BY workflow.target_locale_id
Total job size (word count) by target locale
Select locale.iso_lang_code, locale.iso_country_code, SUM(total_word_count) from locale, workflow where workflow.state != "cancelled" and locale.id = workflow.target_locale_id GROUP BY workflow.target_locale_id
Average time to complete activities in hours
Select name, AVG( HOUR( TIMEDIFF(completed_date, accepted_date) ) ) from task_info where state = 'COMPLETED' group by name
Average time to complete activities in hours and days (rounded) by target locale and activity name
Select locale.iso_lang_code, locale.iso_country_code, task_info.name, ROUND( AVG( HOUR( TIMEDIFF(task_info.completed_date, task_info.accepted_date) ) ), 2), ROUND( ( AVG( HOUR( TIMEDIFF(task_info.completed_date, task_info.accepted_date) ) ) / 24 ),1) from workflow, task_info, where task_info.state = 'COMPLETED' and task_info.workflow_id = workflow.iflow_instance_id and locale.id = workflow.target_locale_id group by name,target_locale_id
All active activities, that is the ones that are accepted and not yet completed; without test jobs
Select job.id, job.name, task_info.name, accepted_date, user_id from task_info,workflow,job where task_info.state != "complete" and task_info.accepted_date IS NOT NULL and task_info.completed_date IS NULL and job.name NOT LIKE "%test%" and task_info.workflow_id = workflow.iflow_instance_id and workflow.job_ID = job.ID
All active activities with the users that they are available to
SELECT job.id, job.name As 'Job name', CONCAT(locale.iso_lang_code,'-',locale.iso_country_code) As 'Target Locale', jbpm_taskinstance.name_, jbpm_pooledactor.actorid_ As 'Available to' FROM job, workflow, task_info, locale, jbpm_taskinstance, jbpm_task, jbpm_pooledactor, jbpm_taskactorpool WHERE jbpm_pooledactor.id_ = jbpm_taskactorpool.pooledactor_ AND jbpm_taskactorpool.taskinstance_ = jbpm_taskinstance.id_ AND jbpm_taskinstance.actorid_ IS NULL AND workflow.job_id = job.id AND workflow.iflow_instance_id = task_info.workflow_id AND jbpm_taskinstance.task_ = jbpm_task.ID_ AND jbpm_task.tasknode_ = task_info.task_id AND workflow.target_locale_id = locale.ID AND job.state = 'DISPATCHED'