Pasted

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 (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) PR ON (PR.ID = C.ID)
    LEFT JOIN (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)) 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