update z
set CurrentTimeZoneOffset=convertedOffset
from cptbl z 
join 
(
		select *,
				case 
					when offset ='' then 0
					when offset='999' then 0
					when substring(offset,1,1) in ('0','1','2','3','4','5') then convert(smallint, convert(varbinary(2), '0'+offset, 2))
					else convert(smallint, convert(varbinary(2), 'F'+offset, 2))
				end convertedOffset
		from (
				SELECT [iCpId]
					  ,c.account
					  ,substring(originalMsg,18,8) acc2
					  ,substring(originalMsg,26,3) offset

				  FROM [ActvtRtProcQueueTbl] r
					join cptbl c on c.cpid=r.icpid and Deleted is null
				  where r.ServerUtcTime=(select max(serverutctime) from [ActvtRtProcQueueTbl] r2 where r2.iCpId=r.iCpId)
		) x
		group by x.Account,acc2,x.iCpId,x.offset
) y on y.iCpId=z.CpId
