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