Merging Task Notes into a Task

Merging Task Notes into a Task



  1. -- This script will take notes on a Task and append them to the Task's Description.
  2. -- This is used on systems that support Tasks but don't support notes on them.

  4. DECLARE @Header NVARCHAR(100) = 'Task Comments'

  6. UPDATE
  7.   __M_Common_Notes
  8. SET
  9.   Final_Status = 'Deleted'
  10. WHERE 1=1
  11.   AND Final_Parent_Type = '__M_Tasks'

  13. ;WITH P AS
  14. (
  15.   SELECT
  16.     n.Final_Parent_Id,
  17.     CONCAT(@Header, CHAR(10), CHAR(10)) + 
  18.     STRING_AGG(nt.NoteText, CONCAT(CHAR(10), ' ----- ', CHAR(10))) WITHIN GROUP (ORDER BY  fd.FinalDate DESC) NoteText
  19.   FROM
  20.     __M_Common_Notes n
  21.       LEFT OUTER JOIN
  22.     __M_Users u
  23.       ON n.Final_Created_By_Id = u.Id
  24.       CROSS APPLY(SELECT 
  25.         NULLIF(u.Final_FullName, '') FullName,
  26.         NULLIF(u.Final_LastName, '') LastName,
  27.         NULLIF(u.Final_FirstName, '') FirstName
  28.         ) fn
  29.       CROSS APPLY(SELECT
  30.         CONCAT_WS(', ', fn.LastName, fn.FirstName) LastFirstName
  31.         ) lfn
  32.       CROSS APPLY(SELECT CONCAT_WS(': ', 'Created By', COALESCE(fn.FullName, lfn.LastFirstName)) CreatedBy) cb
  33.       CROSS APPLY(SELECT COALESCE(n.Final_Date, GREATEST(n.Final_Created_At, n.Final_Updated_At)) FinalDate) fd
  34.       CROSS APPLY(SELECT CONCAT_WS(': ', 'Created At', FORMAT(fd.FinalDate, 'yyyy-MM-dd hh:mm tt')) CreatedAt) ca
  35.       CROSS APPLY(SELECT CONCAT_WS(CHAR(10),  cb.CreatedBy, ca.CreatedAt, n.Original_Description_Text) NoteText) nt
  36.   WHERE 1=1
  37.     AND Final_Parent_Type = '__M_Tasks'
  38.   GROUP BY
  39.     Final_Parent_Id
  40. )

  42. UPDATE
  43.   t
  44. SET
  45.   Final_Description_Text = CONCAT(Original_Description_Text, CHAR(10), CHAR(10), P.NoteText)
  46. FROM
  47.   __M_Tasks t
  48.     INNER JOIN
  49.   P
  50.     ON t.Id = P.Final_Parent_Id


  53. SELECT
  54.   Final_Description_Text,
  55.   Original_Description_Text
  56. FROM
  57.   __M_Common_Notes
  58. WHERE 1=1
  59.   AND Final_Description_Text LIKE CONCAT('%', @Header, '%')
  60.   AND Final_Description_Text != Original_Description_Text