When the client asks for a unique ID based on name and address but gives you dirty data

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
exhausted
my work is done.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.