Useful SQL database queries

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'