# 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)

## 0 Comments:

## Post a Comment

Note: Only a member of this blog may post a comment.