SQL Spreadsheets: Cherry Picking Matters

SQL Spreadsheets: Cherry Picking Matters

The following SQL command will generate a spreadsheet that can be used to cherry-pick specific matters.

  1. CREATE OR ALTER FUNCTION SafeSingleLine (
  2. @Input NVARCHAR(MAX)
  3. ) RETURNS NVARCHAR(MAX) AS BEGIN
  4. DECLARE @Ret NVARCHAR(MAX) = COALESCE(@Input, '')

  5. SET @RET = REPLACE(@RET, CHAR(13), ' ')
  6. SET @RET = REPLACE(@RET, CHAR(10), ' ')
  7. SET @RET = REPLACE(@RET, CHAR(9), ' ')
  8. SET @RET = REPLACE(@RET, '''', ' ')
  9. SET @RET = REPLACE(@RET, ',', ' ')

  10. WHILE(CHARINDEX(' ', @RET) != 0) BEGIN
  11. SET @RET = REPLACE(@RET, ' ', ' ')
  12. END

  13. SET @Ret = TRIM(@RET)

  14. RETURN @RET
  15. END
  16. GO

  17. SELECT
  18. '' as 'Keep? (Y)',
  19. V1.Id,
  20. dbo.SafeSingleLine(V2.Final_FullName) as 'Final_FullName',
  21. dbo.SafeSingleLine(V1.Final_ReferenceCode) as 'Final_ReferenceCode',
  22. dbo.SafeSingleLine(V1.Final_Subject) as 'Final_Subject',
  23. dbo.SafeSingleLine(V1.Final_Description) as 'Final_Description',
  24. V1.Final_Status,
  25. Script = CONCAT(''
  26. --IF cell A### = "Y"
  27. , '=IF(INDIRECT("A"&ROW()) = "Y",'
  28. , '"'
  29. , ' UPDATE'
  30. , ' __M_Matters'
  31. , ' SET'
  32. , ' Final_Status = Original_Status '
  33. , ' WHERE 1=1'
  34. , ' AND Id = '
  35. , ' ''" & '
  36. , ' INDIRECT("B"&ROW()) '
  37. , ' & "'''
  38. , '"'
  39. --/THEN
  40. , ', '
  41. --ELSE
  42. , '"'
  43. , ' '
  44. , '"'
  45. --/ELSE
  46. , ')'
  47. )
  48. FROM
  49. __M_Matters V1
  50. LEFT JOIN
  51. __M_Contacts V2 ON V2.Id = V1.Final_ClientContact_Id
  52. ORDER BY
  53. V1.Final_ReferenceCode

    • Related Articles

    • SQL Spreadsheets: Cherry-Picking Records

      As a general rule, we recommend that SQL scripts be used to mass-select/prune records; however, sometimes that is not possible. In these cases, we recommend having a member of the law firm complete a Google spreadsheet that clearly indicates what ...
    • SQL Spreadsheets: Cherry-Picking Custom Fields

      The following SQL command will generate a Google Sheet that users can use to cherry-pick custom field definitions. SELECT '' as 'Keep? (Y)', V1.Id, V1.Final_Parent_Type, V1.Final_Kind, V1.Final_Subject, V1.Final_Description, V1.Final_Status, ...
    • SQL Spreadsheets: Reassigning Practice Areas

      Sometimes users want to reassign practice areas as part of the data restore into a new system. The following process makes it easy. Generate the SQL Spreadsheet Run the following command then copy the results and the headers into a Google Doc. ...
    • SQL Spreadsheets: DocType Mappings

      The following SQL script will generate a Google Spreadsheet that will allow users to override document category names. This is extremely helpful when merging systems. SELECT V1.Id, V1.Final_ReferenceCode as 'Old Code', V1.Final_Subject as 'Old Name', ...
    • 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)', ...