Hi,

A client has a database where employees and their dependants have the same employee number. The numbers should have been unique so instead of everyone in a family having the same number like "G-0001", The employee should have had "G-0001-01", the spouse (if there is one) "G-0001-02", the first child "G-0001-03" and so on.

How can I generate these numbers? I am guessing a partition using row_number()?

Here's some code to create some test data:

  create table #tempEmp (
  emppk int,
  empfk int,
  lastname varchar(25),
  firstname varchar(25),
  empnum varchar(20),
  empdob date,
  reltype varchar(10))

  insert into #tempEmp 
  (emppk,
  empfk,
  lastname,
  firstname,
  empnum,
  empdob,
  reltype)
  values 
  (1,
  null,
  'Smith',
  'John',
  'G-0001',
  '2000-01-02',
  null),
  (2,
  1,
  'Smith',
  'Jane',
  'G-0001',
  '2001-01-02',
  'Spouse'),
  (3,
  1,
  'Smith',
  'Jim',
  'G-0001',
  '2020-01-03',
  'Child'),
  (4,
  1,
  'Smith',
  'Jenny',
  'G-0001',
  '2021-01-04',
  'Child'),
  (5,
  null,
  'Sumner',
  'Gordon',
  'G-0002',
  '1951-10-02',
  null),
  (6,
  5,
  'Sumner',
  'Joseph',
  'G-0002',
  '1976-09-23',
  'Child')

SELECT emppk,
  empfk,
  lastname,
  firstname,
  empnum,
  empdob,
  reltype
FROM #TempEmp
WHERE empnum is not null and empnum <> ''
  and right(ltrim(rtrim(empnum)), 3) not like '-__'
ORDER BY empnum, empfk, reltype desc, empdob desc

drop table #tempemp

Update:
Maybe this will work for me:

;with cte as (select *, row_number() over 
	(partition by 
        empnum
 order by empnum, empfk, reltype desc, empdob desc
) as Rn
from #TempEmp)

select * from cte