SQL Spreadsheets: Reassigning Practice Areas

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.

  1. DECLARE @CRLF NVARCHAR(MAX) = CHAR(13) + CHAR(10);

  3. SELECT
  4.     '' as 'New Practice Area',
  5.     V1.Id,
  6.     V3.Final_Subject as 'Original Practice Area',
  7.     V2.Final_FullName,
  8.     V1.Final_ReferenceCode, 
  9.     V1.Final_Subject, 
  10.     REPLACE(V1.Final_Description, @CRLF, '') as 'Final_Description',
  11.     V1.Final_Status,
  12.     Script =
  13.       --IF cell A### = "Y"
  14.       '=IF(INDIRECT("A"&ROW()) <> ""'
  15.       --THEN: Generate the script using the ID in cell B###.
  16.       + ', "Update __M_Matters SET Final_PracticeArea_Id = ''" & INDIRECT("A"&ROW()) & "'' WHERE Id = ''" & INDIRECT("B"&ROW()) & "''"'
  17.       --ELSE: Blank script
  18.       + ', ""'
  19.       + ')'
  20. FROM
  21.     __M_Matters V1
  22. LEFT JOIN
  23.     __M_Contacts V2 ON V2.Id = V1.Final_ClientContact_Id
  24. LEFT JOIN
  25.     __M_Matters_PracticeAreas V3 ON V3.Id = V1.Final_PracticeArea_Id
  26. ORDER BY
  27.     V1.Final_ReferenceCode

Next Steps

After the client fills in the spreadsheet, run the generated SQL and then this command:

  1. --Link matters to practice areas that already exist
  2. UPDATE
  3. 	__M_Matters
  4. SET
  5. 	Final_PracticeArea_Id = V2.Id
  6. FROM
  7. 	__M_Matters V1,
  8. 	__M_Matters_PracticeAreas V2
  9. WHERE
  10. 	V1.Final_PracticeArea_Id = V2.Final_Subject

Then run this command to create any missing practice areas.



    • Related Articles

    • Synthesize Missing Practice Areas

      The following SQL script will create practice areas where they do not exist in the Matters table. --Synthesize missing practice areas from Matters INSERT INTO __M_Matters_PracticeAreas( Id, Final_Subject ) SELECT DISTINCT Final_PracticeArea_Id, ...

    • Delete unused Practice Areas

      The following SQL script will delete unused practice areas. --Delete any practice areas that are not used UPDATE __M_Matters_PracticeAreas SET Final_Status = 'Deleted' WHERE 1=1 AND Id NOT IN( SELECT Final_PracticeArea_Id FROM __M_Matters WHERE ...

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

    • SQL Spreadsheets: Manually Prioritizing Matters

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