case
when len(
ROW_NUMBER() OVER(
PARTITION BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3)
ORDER BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3)
)
) < 2

then (UPPER
(left
(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3) + '0' + convert(varchar(3),ROW_NUMBER() OVER( PARTITION BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3) ORDER BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3)))
)
)

else (UPPER(left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3) + convert(varchar(3),ROW_NUMBER() OVER( PARTITION BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3) ORDER BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3)))))

end as ID