SQL Spreadsheets: Manually Prioritizing Matters

SQL Spreadsheets: Manually Prioritizing Matters



  1. --The following SQL will generate a list of matters and allow their priorities to easily be specified.
  2. CREATE OR ALTER FUNCTION SafeSingleLine (
  3. @Input NVARCHAR(MAX)
  4. ) RETURNS NVARCHAR(MAX) AS BEGIN
  5. DECLARE @Ret NVARCHAR(MAX) = COALESCE(@Input, '')

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

  13. WHILE(CHARINDEX('  ', @RET) != 0) BEGIN
  14. SET @RET = REPLACE(@RET, '  ', ' ')
  15. END

  17. SET @Ret = TRIM(@RET)

  19. RETURN @RET
  20. END
  21. GO

  23. SELECT
  24.     V1.Batch_Group as 'Original Priority',
  25.     '' as 'New Priority',
  26.     V1.Id, 
  27.     dbo.SafeSingleLine(V2.Final_FullName) as 'Final_FullName',
  28.     dbo.SafeSingleLine(V1.Final_ReferenceCode) as 'Final_ReferenceCode', 
  29.     dbo.SafeSingleLine(V1.Final_Subject) as 'Final_Subject', 
  30.     dbo.SafeSingleLine(V1.Final_Description) as 'Final_Description',
  31.     V1.Final_Status,
  32.     Script = CONCAT(''
  33.       --IF cell A### = "Y"
  34.       , '=IF(INDIRECT("B"&ROW()) <> "",'
  35.   , '"'
  36.   , ' UPDATE'
  37.   , '   __M_Matters'
  38.   , ' SET'
  39.   , '   Batch_Group = '
  40.       , '     ''" & '
  41.   , '       INDIRECT("B"&ROW()) '
  42.   , '     & "'''
  43.   , ' WHERE 1=1'
  44.   , '   AND Id = '
  45.   , '     ''" & '
  46.   , '       INDIRECT("C"&ROW()) '
  47.   , '     & "'''
  48.   , '"'
  49.   --/THEN
  50.       , ', '
  51.   --ELSE
  52.   , '"'
  53.   , ' '
  54.   , '"'
  55.   --/ELSE
  56.       , ')'
  57.   )
  58. FROM
  59.     __M_Matters V1
  60. LEFT JOIN
  61.     __M_Contacts V2 ON V2.Id = V1.Final_ClientContact_Id
  62. ORDER BY
  63.     V1.Final_ReferenceCode

    • Related Articles

    • SQL Spreadsheets: Cherry Picking Matters

      The following SQL command will generate a spreadsheet that can be used to cherry-pick specific matters. CREATE OR ALTER FUNCTION SafeSingleLine ( @Input NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Ret NVARCHAR(MAX) = COALESCE(@Input, '') ...

    • 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: 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: 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)', ...