with InfoProcedimientos (ID, Procedimientos, Realizados, Terminados)
as
(
	select CA.ID as ID, COUNT(P.ID) as Procedimientos, COUNT(P.RealizadoPorId) as Realizados, COUNT(P.RevisadoPorId) as Terminados
	from ControlAuditoria CA
		inner join ProgramaTrabajo PT on (CA.ProgramaTrabajo_ID = PT.ID)
		inner join Objetivos O on (O.ProgramaTrabajoId = PT.ID)
		inner join Procedimientos P on (P.ObjetivoId = O.ID)
	where P.NoAplica = 0
	group by CA.ID, CA.Nombre
),
UltimoInforme (Nombre, ControlAuditoriaId)
as 
(
	select CV.Nombre, VA.ControlAuditoriaId 
	from DefinicionVersionInforme CV
		left join VersionInformeAuditoria VA on (VA.DefinicionVersionInforme_ID = CV.ID)
	where VA.ID = (select MAX(ID) from VersionInformeAuditoria V2 
				   where V2.ControlAuditoriaId = VA.ControlAuditoriaId group by V2.ControlAuditoriaId)
)
select distinct PC.Nombre as PlanControl, PC.AnyoDelPlan, MT.Nombre as Marco, C.ID, Lote.Nombre as Lote, C.Nombre, W.StateKey as Estado, I.Nombre as Informe, Procedimientos, Realizados, Terminados
from ControlAuditoria C
	inner join LoteControl LC on (C.ID = LC.ControlAuditoriaId)
	inner join Lote on (LC.LoteId = Lote.ID)
	inner join PlanControl PC on (PC.ID = Lote.PlanControlId)
	inner join MarcosTrabajo MT on (MT.ID = PC.MarcoTrabajoId)
	inner join WorkflowPersistence W on (W.EntityId = C.ID)
	inner join InfoProcedimientos PR on (PR.ID = C.ID)
	left join UltimoInforme I on (I.ControlAuditoriaId = C.ID)
where MT.IsDeleted = 0
	and PC.IsDeleted = 0
	and C.IsDeleted = 0
	and W.WorkflowTypeIdentifier = 'controlauditoria'
order by PC.Nombre, PC.AnyoDelPlan desc, MT.Nombre