Finding Duplicate Assignments SP

Share on facebook
Share on twitter
Share on linkedin
Check for duplicate assignments as the OLAP cube does not pick these up.  If
users have entered time against the dupe then it will not reflect in the cube.
 
Run the following query against the Project Server database to ascertain
whether there are any dupes:-
 
 
--To get the duplicate web-assignments
 
CREATE PROCEDURE dbo.MS_PSS_GetDuplicateWebAssignmentList_Details
 
AS
 
CREATE TABLE #DuplicateAssignmentsList
(
ProjectName nvarchar(500),
ResourceName nvarchar(500),
Assignment nvarchar(500),
WASSN_ID int,
WASSN_REMOVED_BY_RESOURCE int,
CreatedViaSelfAssign bit,
WASSN_CREATED_BY_RES int,
ASSN_ACT_WORK float,
WebActualWorkTotal float,
LastDayAWTracked datetime,
WASSN_SEND_UPDATE_DATE datetime,
NumberOfDuplicates int,
ProjectManager nvarchar(500),
PendingPMApproval int,
PendingResourceSubmission int,
PROJ_CREATION_DATE datetime,
WPROJ_LAST_PUB datetime
)
 
 
DECLARE @WASSN_ID int
DECLARE @WPROJ_ID int
DECLARE @WRES_ID int
DECLARE @TASK_NAME varchar(500)
DECLARE @TASK_UID int
DECLARE @TASK_UID_SUMMARY int
DECLARE @TASK_IS_SUMMARY int
DECLARE @WASSN_REMOVED_BY_RESOURCE int
DECLARE @WASSN_DELETED_IN_PROJ int
DECLARE @WASSN_ASSIGNED_TO_EXISTING int
DECLARE @WASSN_CREATED_BY_RES int
DECLARE @ASSN_ACT_WORK decimal
DECLARE @WASSN_SEND_UPDATE_DATE datetime
DECLARE @WRES_ID_MGR int --PM id
DECLARE @WASSN_ACTUALS_PENDING int --manager has pending updates
DECLARE @WASSN_UPDATE_STATUS int -- resource has updated, but not send to
manager
 
DECLARE WebAssignments CURSOR FOR
SELECT WASSN_ID,
WPROJ_ID,
WRES_ID,
TASK_NAME,
TASK_UID,
TASK_UID_SUMMARY,
TASK_IS_SUMMARY
, WASSN_REMOVED_BY_RESOURCE,
WASSN_DELETED_IN_PROJ,
WASSN_ASSIGNED_TO_EXISTING,
WASSN_CREATED_BY_RES,
ASSN_ACT_WORK,
WASSN_SEND_UPDATE_DATE,
WRES_ID_MGR,
WASSN_ACTUALS_PENDING,
WASSN_UPDATE_STATUS FROM MSP_WEB_ASSIGNMENTS
 
OPEN WebAssignments
 
FETCH NEXT FROM WebAssignments
INTO @WASSN_ID,
@WPROJ_ID,
@WRES_ID,
@TASK_NAME,
@TASK_UID,
@TASK_UID_SUMMARY,
@TASK_IS_SUMMARY,
@WASSN_REMOVED_BY_RESOURCE,
@WASSN_DELETED_IN_PROJ,
@WASSN_ASSIGNED_TO_EXISTING,
@WASSN_CREATED_BY_RES,
@ASSN_ACT_WORK,
@WASSN_SEND_UPDATE_DATE,
@WRES_ID_MGR,
@WASSN_ACTUALS_PENDING,
@WASSN_UPDATE_STATUS
 
 
 
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DuplicateCount int
SET @DuplicateCount = 0
 
IF @WASSN_DELETED_IN_PROJ <> 1 AND @TASK_IS_SUMMARY = 0
BEGIN
SET @DuplicateCount = (SELECT COUNT(*) FROM MSP_WEB_ASSIGNMENTS WHERE
TASK_UID = @TASK_UID
--AND (TASK_UID_SUMMARY = @TASK_UID_SUMMARY OR @TASK_UID_SUMMARY IS
NULL)
AND TASK_IS_SUMMARY = 0
AND WPROJ_ID = @WPROJ_ID
AND WRES_ID = @WRES_ID
AND WASSN_DELETED_IN_PROJ = 0)
END
--temp code to test by deleting all triplicates/quadruplicates
IF (@DuplicateCount >2)
BEGIN
declare @temp int
--UPDATE MSP_WEB_ASSIGNMENTS SET RESERVED_DATA1 = 121212 WHERE WASSN_ID
= @WASSN_ID
--DELETE FROM MSP_WEB_ASSIGNMENTS WHERE RESERVED_DATA1 = 121212
END
 
IF (@DuplicateCount >1)
BEGIN
DECLARE @PROJECT_NAME nvarchar(500)
SET @PROJECT_NAME = (SELECT PROJ_NAME FROM MSP_WEB_PROJECTS WHERE
WPROJ_ID = @WPROJ_ID)
 
DECLARE @RESOURCE_NAME nvarchar(500)
SET @RESOURCE_NAME = (SELECT RES_NAME FROM MSP_WEB_RESOURCES WHERE
WRES_ID = @WRES_ID)
 
DECLARE @ProjectManager nvarchar(500)
SET @ProjectManager = (SELECT RES_NAME FROM MSP_WEB_RESOURCES WHERE
WRES_ID = @WRES_ID_MGR)
 
DECLARE @WebActualWorkTotal float
DECLARE @LastDayAWTracked datetime
 
DECLARE @WPROJ_LAST_PUB datetime
SET @WPROJ_LAST_PUB = (SELECT WPROJ_LAST_PUB FROM MSP_WEB_PROJECTS WHERE
WPROJ_ID = @WPROJ_ID)
 
DECLARE @PROJ_CREATION_DATE datetime
SET @PROJ_CREATION_DATE = (SELECT PROJ_CREATION_DATE FROM MSP_PROJECTS
WHERE PROJ_ID = (SELECT PROJ_ID FROM MSP_WEB_PROJECTS WHERE WPROJ_ID =
@WPROJ_ID))
--    SELECT PROJ_CREATION_DATE FROM MSP_PROJECTS WHERE PROJ_ID = -1
 
EXEC MS_PSS_GetWebWorkTotalByWassnID @WASSN_ID, @WebActualWorkTotal
OUTPUT, @LastDayAWTracked OUTPUT
 
INSERT INTO #DuplicateAssignmentsList
(
ProjectName,
ResourceName,
Assignment,
WASSN_ID,
WASSN_REMOVED_BY_RESOURCE,
CreatedViaSelfAssign,
WASSN_CREATED_BY_RES,
ASSN_ACT_WORK,
WebActualWorkTotal,
LastDayAWTracked,
WASSN_SEND_UPDATE_DATE,
NumberOfDuplicates,
ProjectManager,
PendingPMApproval,
PendingResourceSubmission,
PROJ_CREATION_DATE,
WPROJ_LAST_PUB
)
VALUES(
 
@PROJECT_NAME,
@RESOURCE_NAME,
@TASK_NAME,
@WASSN_ID,
@WASSN_REMOVED_BY_RESOURCE,
@WASSN_ASSIGNED_TO_EXISTING,
@WASSN_CREATED_BY_RES,
@ASSN_ACT_WORK/60/1000,
@WebActualWorkTotal/60/1000,
@LastDayAWTracked,
@WASSN_SEND_UPDATE_DATE,
@DuplicateCount,
@ProjectManager,
@WASSN_ACTUALS_PENDING,
@WASSN_UPDATE_STATUS,
@PROJ_CREATION_DATE,
@WPROJ_LAST_PUB
)
 
END
 
 
 
FETCH NEXT FROM WebAssignments
INTO @WASSN_ID,
@WPROJ_ID,
@WRES_ID,
@TASK_NAME,
@TASK_UID,
@TASK_UID_SUMMARY,
@TASK_IS_SUMMARY,
@WASSN_REMOVED_BY_RESOURCE,
@WASSN_DELETED_IN_PROJ,
@WASSN_ASSIGNED_TO_EXISTING,
@WASSN_CREATED_BY_RES,
@ASSN_ACT_WORK,
@WASSN_SEND_UPDATE_DATE,
@WRES_ID_MGR,
@WASSN_ACTUALS_PENDING,
@WASSN_UPDATE_STATUS
 
END
 
CLOSE WebAssignments
DEALLOCATE WebAssignments
 
SELECT NumberOfDuplicates,
ProjectName,
ResourceName,
Assignment,
WASSN_ID,
CreatedViaSelfAssign,
WASSN_CREATED_BY_RES,
WebActualWorkTotal,
LastDayAWTracked,
ProjectManager,
PendingPMApproval,
PendingResourceSubmission,
WASSN_SEND_UPDATE_DATE,
PROJ_CREATION_DATE,
WPROJ_LAST_PUB
 
FROM #DuplicateAssignmentsList
ORDER BY NumberOfDuplicates, ProjectName, ResourceName, Assignment asc
 
DROP TABLE #DuplicateAssignmentsList
 
 
--To Delete the duplicate web-assignements
CREATE PROCEDURE dbo.MS_PSS_DeleteWebAssignmentByAssnID
@wassn_id_remove varchar(200)
AS
 
DELETE FROM MSP_WEB_ASSIGNMENTS WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_WORK WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_WORK_ADJUSTED WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_WORK_APPROVAL WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_WORKGROUP_FIELDS WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_TRANSACTIONS WHERE WASSN_ID=@wassn_id_remove

 

Leave a Reply

Sign up for Softvative Newsletter

Receive the latest technology and leadership news and resources from us

Subscribe To Softvative Newsletter

Get notified about new articles

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest