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. );

    • Related Articles

    • Moving Documents into Matters that are Nested under Calendar Entries

      --The following script moves documents that are nested under Calendar Entries to be nested under Matters instead. UPDATE __M_Documents_Digital_Files SET Final_Folder_RelativePath = '\Calendar Attachments\', Final_Parent_Type = ...

    • Tasks: Append Assigned To and Completed By to Descriptions

      The script below will append the text 'Assigned to User: {firstName lastName}' and 'Completed by User: {firstName lastName}' to the end of the task description in cases where the users Email address was changed on the __M_Users table as a result of ...

    • Copy Calendar Entry Dates into Custom Field Values

      Some systems let you create custom fields that reference a Calendar Entry. When moving custom fields into a system that does not support this, an easy workaround would be to turn these custom fields into DateTime custom fields by copying in the start ...

    • SQL Spreadsheets: User Mappings

      The following SQL script will generate a Google Spreadsheet that will allow users to override email addresses. This is extremely helpful when merging old user accounts together. SELECT V1.Id, V1.Final_FullName as 'Old User (FullName)', ...

    • Moving Documents into Matters that are Nested under Tasks

      --The following script moves documents that are nested under Tasks to be nested under Matters instead. UPDATE __M_Documents_Digital_Files SET Final_Folder_RelativePath = '\Task Attachments\', Final_Parent_Type = Relationships.Final_RelatedTo_Type, ...