Combine multiple strategies to generate a unique column?

Question

I have an employee table with last_name and first_name columns. I want to generate a column called employee_code which will contain always 3 characters and will be unique. If it does not already exists, the code will be the first letter of first_name followed by two first letters of last name.

SELECT 'LUDOVIC' AS first_name, 'AUBERT' AS last_name, 'LAU' AS employee_code;

But if 'LAU' is already present, I want to use an alternate strategy, such as using the two first letters of first name followed by first letter of last name.

SELECT 'LUDOVIC' AS first_name, 'AUBERT' AS last_name, 'LUA' AS employee_code;

How can you achieve this in SQL?

Answer 1

DROP TABLE IF EXISTS employe;
CREATE TABLE employe (
id int primary key,
first_name varchar(30),
last_name varchar(30),
code1 char(3),
nb1 int NULL,
code2 char(3),
code char(3) NULL
);
insert into employe (id, first_name, last_name, code1, code2)
VALUES (1,'LUDOVIC','AUBERT','LAU','LUA'),(2,'LUDOVICO','AUBERT','LAU','LUA');

SELECT * FROM employe;
WITH cte AS (
    SELECT id, first_name, last_name, code1, rank() OVER (PARTITION BY code1 ORDER BY id ASC) as n1, code2
    FROM employe
)  
SELECT *, CASE WHEN n1=1 THEN code1 ELSE code2 END AS code FROM cte;