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, '')
- SET @RET = REPLACE(@RET, CHAR(13), ' ')
- SET @RET = REPLACE(@RET, CHAR(10), ' ')
- SET @RET = REPLACE(@RET, CHAR(9), ' ')
- SET @RET = REPLACE(@RET, '''', ' ')
- SET @RET = REPLACE(@RET, ',', ' ')
- WHILE(CHARINDEX(' ', @RET) != 0) BEGIN
- SET @RET = REPLACE(@RET, ' ', ' ')
- END
- SET @Ret = TRIM(@RET)
- RETURN @RET
- END
- GO
- SELECT
- '' as 'Keep? (Y)',
- V1.Id,
- dbo.SafeSingleLine(V2.Final_FullName) as 'Final_FullName',
- dbo.SafeSingleLine(V1.Final_ReferenceCode) as 'Final_ReferenceCode',
- dbo.SafeSingleLine(V1.Final_Subject) as 'Final_Subject',
- dbo.SafeSingleLine(V1.Final_Description) as 'Final_Description',
- V1.Final_Status,
- Script = CONCAT(''
- --IF cell A### = "Y"
- , '=IF(INDIRECT("A"&ROW()) = "Y",'
- , '"'
- , ' UPDATE'
- , ' __M_Matters'
- , ' SET'
- , ' Final_Status = Original_Status '
- , ' WHERE 1=1'
- , ' AND Id = '
- , ' ''" & '
- , ' INDIRECT("B"&ROW()) '
- , ' & "'''
- , '"'
- --/THEN
- , ', '
- --ELSE
- , '"'
- , ' '
- , '"'
- --/ELSE
- , ')'
- )
- FROM
- __M_Matters V1
- LEFT JOIN
- __M_Contacts V2 ON V2.Id = V1.Final_ClientContact_Id
- ORDER BY
- V1.Final_ReferenceCode