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.
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”.
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.