This post continues from the previous post Part 2.

In this post we are working with three tables. In the previous post we only needed one table.

We have our table, re-named to [People06] for this post, that contains a new batch of data. This will have to be appended to out “historical” table called [AllPeople]. This historical table keeps a record of all of the batches that have ever arrived. We need to sequence them with sequence numbers.

People06

AllPeople

How do we know what the next sequence number is? For example, Bob is listed one time in the AllPeople table and the code is “CodeAB001”. The next number for Bob will be “CodeAG002”, followed by “AG003” and “AG004”, which are currently items in the new data coming in, listed in the table People06. As another example, Jill’s highest sequence number so far is “003”. Any new data coming in for Jill will start with “004”. Therefore, her next entry will be “CodeAG004”.

AllPeopleMaxSeq

This is a new table. It will be a subset of our [AllPeople] table. It will only list the names of the people once, followed by the maximum sequence number so far. Here are the results.

Here is the script that creates the table and then builds the data in the table.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AllPeopleMaxSeq]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[AllPeopleMaxSeq]
		
create table [dbo].[AllPeopleMaxSeq]
(
	firstname varchar (15),
	maxseqnum int
)
insert into [AllPeopleMaxSeq] (firstname, maxseqnum)
	select firstname, max(cast(substring(refnum,7,3) as int)) as maxseqnum
	from AllPeople
	group by firstname;

Update column prevmax in table People06

Now we need to update the column prevmax in the table People06 to reflect the values in the table AllPeopleMaxSeq for those people that exist in the table AllPeopleMaxSeq. This will give us values that we can add to the values we will soon get in the column dupseq in People06. That total (integer) will then be converted to a string with leading zeros and then appended to the refnum in People06. After that, we can simply insert the rows in People06 to the “history” table [AllPeople]. We are now complete, except that our table [AllPeopleMaxSeq] is out of date. That is easy to fix however.

Here below is the code that updates the column prevmax in the table People06.

update [People06]
set [People06].[prevmax] = [AllPeopleMaxSeq].[maxseqnum]
from [People06]
inner join [AllPeopleMaxSeq] 
on [People06].[firstname] = [AllPeopleMaxSeq].[firstname]

Here are the results of a select query on People06 after running the above update.

Generate the Sequence Numbers

The next step is to generate the sequence numbers and store them in the table People06 in the column dupseq. Please refer to the previous post for how to do that. Here we will just present the code below.

update People06 
Set People06.dupseq=R.dupseq
from(select unqid,ROW_NUMBER() over (partition by S.firstname  order by S.unqid) as dupseq
		from People06 S) R
where People06.unqid=R.unqid

Build refnum In People06

The final step is to build the reference number. We need to add dupseq and prevmax together, convert that to characters, and put leading zeros in front of it to get exactly three characters that we then concatenate to the existing refnum for each row.

UPDATE People06
SET refnum = refnum + cast(right('000' + cast((dupseq + prevmax) as varchar(3)),3) as char(3)) 

Here below are the final results.