Point taken! Overkill.

Thanks Johnathon, Your response crossed with my solution and are pretty much the same.

Cheers!

I think I've sussed it now. I have amended my code for the field NewReg to the following...

Case

When Reg_Type <> 'Reg' then

CONCAT('CH',

Right(10000+

Convert(CHAR(4),row_number() over (partition by Reg_Type order by PolicyRef@)),4),

'SIS')

ELSE Reg...

My forst go at a solution is as follows and gives me almost what I'm looking for.

I just  need to have the numbering in the format '0001', '0002', '0003' etc.....

Thanks, I think that's what I need.

I'll have to play with this and my existing code... probably need to introduce this as another step before extracting the final output.

Will give...

Hi Jonathon,

Thanks for that. Gives me a little more confidence.
You are probably right.  I guess I'll go for contracts rather than seeking a permanent position as it...

I have the same issue with a linked server.

Did you manage to resolve this one?

Thanks,

Ged