if exists (select 1 from sys.objects where name = 'udf_queue_status')
   drop function dbo.udf_queue_status
go

create function dbo.udf_queue_status()
returns table 
as
   return (
              with cte(total, waiting, running, finished, succeeded, failed)
              as
                  (
                    select (select count(*) from pmaster..exec_queue) as total
                    , (select count(*) from pmaster..exec_queue where worker_start_time is null and worker_end_time is null) as "waiting"
                    , (select count(*) from pmaster..exec_queue where worker_start_time is not null and worker_end_time is null) as "running"
                    , (select count(*) from pmaster..exec_queue where worker_start_time is not null and worker_end_time is not null) as "finished"
                    , (select count(*) from pmaster..exec_queue where return_code = 0) as "succeeded"
                    , (select count(*) from pmaster..exec_queue where return_code > 0) as "failed"
                  )
               select total, waiting, running, finished, (cast(finished as real) / cast(coalesce(nullif(total,0),1) as real))  * 100 as percent_complete, succeeded, failed
               from cte
              )
go

EXECUTE sp_ms_marksystemobject 'udf_queue_status'
go