cuadro de mando auditorias 2

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