[FIXED] How to update SQL column with a CTE based on multiple cases

Issue

I have the following table:

I need to update the ActivityCode column like this:

• if the third sequence of numbers ( by splitting it with . as separator ) is between 1 and 24, then add the sequence 02 before it. Example 1st row should be: 110.153.02.11
• if the third sequence of numbers ( by splitting it with . as separator ) is between 25 and 49, then add the sequence 01 before it, but for the last sequence start the count from 1. Example 4th row should be: 110.153.01.01

More examples:

110.153.26 --> 110.153.01.02

110.153.27 --> 110.153.01.03

I tried something like this so far, but have zero idea how to continue:

WITH cte
     AS (SELECT WorkActivityTypeId,
                ActivityCode,
                CASE
                  WHEN LEFT(SUBSTRING([ActivityCode],CHARINDEX('.', [ActivityCode])+1, LEN([ActivityCode]) - CHARINDEX('.', [ActivityCode]) ),3) <> '000'
                  THEN  
                    ???
                  ELSE ActivityCode
                END NewActivityCode
         FROM   [VanBerloERP_Acc].[dbo].[WorkActivityTypesTest])
UPDATE cte
SET    ActivityCode = NewActivityCode; 

Solution

For cases when the right 2 digits are 1 through 24, you just need to stuff '.02' into the string.

For cases when the right 2 digits are 25 through 49, you need to add '.01' to the first pair, and then subtract 24 from the last number and re-append it. This involves a bit of converting back and forth and it looks messy the way I did it. Should work though.

I've assumed that the first 2 numbers are always 3 digits and the last two numbers are always 2 digits. If that isn't the case, then this gets messier.

Finally, as a precaution, if the number isn't expected, it just writes the original number back. This could also be avoided by a judicious WHERE clause.

This may not be the best way, but it should work.

UPDATE
        [VanBerloERP_Acc].[dbo].[WorkActivityTypesTest]
   SET
        ActivityCode =
            CASE WHEN TRY_PARSE( SUBSTRING( ActivityCode, 9, 2 ) AS INT )
                      BETWEEN 1 AND 24
                 THEN STUFF( ActivityCode, 8, 0, '.02' )
                 WHEN TRY_PARSE( SUBSTRING( ActivityCode, 9, 2 ) AS INT )
                      BETWEEN 25 AND 49
                 THEN CONCAT(
                              SUBSTRING( ActivityCode, 1, 7 )
                             ,'.01.'
                             ,RIGHT(
                                     CONCAT(
                                             '00'
                                            ,LTRIM(
                                                    STR( 
                                                         TRY_PARSE( RIGHT( ActivityCode, 2 ) AS INT ) - 24
                                                        ,2
                                                        ,0
                                                       )
                                                  )
                                           )
                                    ,2
                                   )
                            )
                 ELSE ActivityCode
            END
;

Answered By - StoneGiant

Answer Checked By - Gilberto Lyons (PHPFixing Admin)