Procedure Revisions with Incorrect Item Count

Provides a list of all revisions with incorrect item count.

Resolution:

Execute the following script to fix the item count of procedure revisions:

;WITH RevisionsWithoutSuspendedCount AS(

SELECT

Procedure_Revision_FK, COUNT(*)AS activeTaskCount

FROM ISMGT_ITEM

WHERE Suspended=0

GROUP BY Procedure_Revision_FK),

ProcedureRevisionWithWrongItemCount AS(

SELECT

pr.Procedure_Revision_PK, prwosc.activeTaskCount

FROM SAT_Procedure_Revisions pr

JOIN RevisionsWithoutSuspendedCount prwosc ON pr.Procedure_Revision_PK = prwosc.Procedure_Revision_FK

WHERE pr.ItemCount > prwosc.activeTaskCount AND pr.ReleasedBy IS NOT NULL)

UPDATE SAT_Procedure_Revisions

SET ItemCount=prwic.activeTaskCount

FROM SAT_Procedure_Revisions pr

JOIN ProcedureRevisionWithWrongItemCount prwic ON pr.Procedure_Revision_PK=prwic.Procedure_Revision_PK