Tasks and Calendar Entries: Removing Duplicate User Assignments

Tasks and Calendar Entries: Removing Duplicate User Assignments

Sometimes the same user is included in a calendar event, or assigned to a task, multiple times.  This can be a result of User Mapping or data error in the legacy system.

Below is an example of how this can happen.

A calendar event includes user John Smith and Henry Jones.  Henry Jones is an inactive user in the legacy system who will not have a user account in the destination system. The firm has decided to map Henry's user data to John Smith, an active user in the destination system.  As a result, the calendar entry includes John Smith twice.  Once because he was originally included in the meeting, and once because Henry Jone's user data was mapped to him.

A similar situation can happen with tasks being assigned to the same user multiple times.

The script below will remove these duplicate user assignments.

  1.   GO
  2. WITH P AS
  3. (
  4. SELECT
  5. cpu.Id,
  6. ROW_NUMBER() OVER (PARTITION BY cpu.Final_CalendarEntry_Id, u.Result_Id ORDER BY (SELECT NULL)) rowNum
  7. FROM
  8. __M_CalendarEntries_Participating_Users cpu
  9. INNER JOIN
  10. __M_Users u
  11. ON u.Id = cpu.Final_User_Id
  12. ),
  13. P2 AS
  14. (
  15. SELECT 
  16. Id
  17. FROM
  18. P
  19. WHERE 1=1
  20. AND rowNum > 1
  21. )
  22. UPDATE
  23. __M_CalendarEntries_Participating_Users
  24. SET
  25. Final_Status= 'Deleted',
  26.         Comment = 'Duplicate assignment removed'
  27. WHERE 1=1
  28. AND Id IN 
  29. (
  30. SELECT Id FROM P2
  31. );



  35. GO
  36. WITH P AS 
  37. (
  38. SELECT 
  39. tau.Id,
  40. ROW_NUMBER() OVER (PARTITION BY tau.Final_TaskEntry_Id, u.Result_Id ORDER BY (SELECT NULL)) rowNum
  41. FROM 
  42. __M_Tasks_AssignedTo_Users tau
  43. INNER JOIN
  44. __M_Users u
  45. ON u.Id = tau.Final_User_Id
  46. ),
  47. P2 AS
  48. (
  49. SELECT
  50. P.Id
  51. FROM
  52. P
  53. WHERE 1=1
  54. AND rowNum  > 1
  55. )
  56. UPDATE
  57. __M_Tasks_AssignedTo_Users
  58. SET
  59. Final_Status = 'Deleted',
  60.         Comment = 'Duplicate assignment removed'
  61. WHERE 1=1
  62. AND Id IN 
  63. (
  64. SELECT
  65. Id
  66. FROM
  67. P2
  68. );