Thanks I got it to work.

>Hi,
>
>My english is poor for explaining windows functions, but many articles about it was wrote.
>
>

>select  *,  
>       empnum+COALESCE('-'+RIGHT('00'+CAST(NULLIF(row_number() over (partition by  empnum 
>                                                                     order by case when reltype is null then 0 
>                                                                                   when reltype='Spouse' then 1 
>                                                                                   When reltype='Child' then 2 ELSE 3 END, empdob
>                                                                     ) -1,0) as VARCHAR(30)),2), '')
>  from #TempEmp
>

>
>MartinaJ
>
>>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