Querys utilitarios en Altiris (parte 1)

query-util-altiris-1.sql

-- Casos creados por alguien
select distinct number,title from workitem
where created_by_worker_contact_id=(select contact_id from worker where upper(name) like '%ARBO%')
order by number desc;

-- Personas asociados a una cola de atención específica
select * from worker where queue_id=455;

-- Cantidad de casos asociados a todos los integrantes de una cola de atención
select     (select w3.name from worker w3 where w3.id= w1.assigned_to_worker_id) asignado_a,count(*) Casos
from workitem w1 inner join (select number,max(version) as version from workitem group by number) w2
on w1.number=w2.number and w1.version=w2.version
where  w1.assigned_to_worker_id in (455) or w1.assigned_to_worker_id  in (select id from worker where queue_id=455)
group by w1.assigned_to_worker_id
order by 2 desc;

-- Fecha de creación de un caso y nombre del usuario que lo creó
Select
convert(nvarchar(10),w1.created_on,103) "Fecha de creacion",
(select w8.name from contact w8 where w8.id=w1.created_by_worker_contact_id ) "Creado Por"
from workitem w1 inner join (select number,min(version) as version from workitem group by number) w2
on w1.number=w2.number and w1.version=w2.version;