This is a quick article on how to use a counter added to the end of a field column of data.
Eh. What?
Assume the following two tables exist:
copyraw
So based on this logic, the Employee "Joel Lipman" has a degree in Computer Science BSc and some qualification in being Superman.TABLE: Employee -------------------------------------------------- ID FirstName Surname 1 Joel Lipman 2 Another Joe TABLE: Qualifications -------------------------------------------------- ID Employee Subject Grade 1 001 Computer Science BSc 2.1 2 001 Superman 10/10 3 002 Tape-Monkey Engineer 1:0
- TABLE: Employee
- --------------------------------------------------
- ID FirstName Surname
- 1 Joel Lipman
- 2 Another Joe
- TABLE: Qualifications
- --------------------------------------------------
- ID Employee Subject Grade
- 1 001 Computer Science BSc 2.1
- 2 001 Superman 10/10
- 3 002 Tape-Monkey Engineer 1:0
Specification
The output expected by the customer is the following:
copyraw
EMP -------------------------------------------------- EmpNumber QualificationRef 001 00101 001 00102 002 00201 -- Qualification Ref is a unique number using the -- Employee Number concatenated with a 2 digit sequence -- number (padded with zeros)
- EMP
- --------------------------------------------------
- EmpNumber QualificationRef
- 001 00101
- 001 00102
- 002 00201
- -- Qualification Ref is a unique number using the
- -- Employee Number concatenated with a 2 digit sequence
- -- number (padded with zeros)
How?
So how do we do it? In the above example, Joel Lipman has two qualification records. So let's start with a simple query:
copyraw
SELECT RIGHT('000' + CAST(e.ID AS VARCHAR), 3) AS EmployeeNo, RIGHT('000' + CAST(q.Employee AS VARCHAR), 3) + '01' AS QualificationRef FROM Qualifications q INNER JOIN Employee e ON RIGHT('000' + CAST(e.ID AS VARCHAR), 3)=q.Employee -- yields EmployeeNo QualificationRef -------------------------------- 001 00101 001 00101
- SELECT
- RIGHT('000' + CAST(e.ID AS VARCHAR), 3) AS EmployeeNo,
- RIGHT('000' + CAST(q.Employee AS VARCHAR), 3)
- + '01' AS QualificationRef
- FROM
- Qualifications q
- INNER JOIN Employee e
- ON RIGHT('000' + CAST(e.ID AS VARCHAR), 3)=q.Employee
- -- yields
- EmployeeNo QualificationRef
- --------------------------------
- 001 00101
- 001 00101
Almost useful... but the qualification reference has to be unique. We're going to add the ROW_NUMBER() to the employee number to create a unique identifier. Try this:
copyraw
SELECT q.Employee AS EmployeeNo, RIGHT('000' + CAST(q.Employee AS VARCHAR), 3) + RIGHT( '00' + CAST( ( ROW_NUMBER() OVER (ORDER BY q.Employee) ) AS VARCHAR ), 2 ) AS QualificationRef FROM Qualifications q INNER JOIN Employee e ON RIGHT('000' + CAST(e.ID AS VARCHAR), 3)=q.Employee -- yields EmployeeNo QualificationRef -------------------------------- 001 00101 001 00102 002 00203 -- Wrong! This should start from 1 for another employee. 002 00204 003 00305
- SELECT
- q.Employee AS EmployeeNo,
- RIGHT('000' + CAST(q.Employee AS VARCHAR), 3)
- +
- RIGHT(
- '00'
- +
- CAST(
- (
- ROW_NUMBER() OVER (ORDER BY q.Employee)
- ) AS VARCHAR
- ), 2
- ) AS QualificationRef
- FROM
- Qualifications q
- INNER JOIN Employee e
- ON RIGHT('000' + CAST(e.ID AS VARCHAR), 3)=q.Employee
- -- yields
- EmployeeNo QualificationRef
- --------------------------------
- 001 00101
- 001 00102
- 002 00203 -- Wrong! This should start from 1 for another employee.
- 002 00204
- 003 00305
Almost There!
So we're using the ROW_NUMBER() functionality with an ORDER BY which seems to work somewhat. But using the code above still produces an error which can only be seen when using more records and more employees.
PARTITION BY
So a sort of "group" action will allow this if we add "PARTITION BY" to the ROW_NUMBER() statement:
copyraw
SELECT q.Employee AS EmployeeNo, RIGHT('000' + CAST(q.Employee AS VARCHAR), 3) + RIGHT( '00' + CAST( ( ROW_NUMBER() OVER ( PARTITION BY q.Employee ORDER BY q.Employee) ) AS VARCHAR ), 2 ) AS QualificationRef FROM Qualifications q INNER JOIN Employee e ON RIGHT('000' + CAST(e.ID AS VARCHAR), 3)=q.Employee -- yields EmployeeNo QualificationRef -------------------------------- 001 00101 001 00102 002 00201 -- Yay! 002 00202 003 00301
- SELECT
- q.Employee AS EmployeeNo,
- RIGHT('000' + CAST(q.Employee AS VARCHAR), 3)
- +
- RIGHT(
- '00'
- +
- CAST(
- (
- ROW_NUMBER() OVER (
- PARTITION BY q.Employee
- ORDER BY q.Employee)
- ) AS VARCHAR
- ), 2
- ) AS QualificationRef
- FROM
- Qualifications q
- INNER JOIN Employee e
- ON RIGHT('000' + CAST(e.ID AS VARCHAR), 3)=q.Employee
- -- yields
- EmployeeNo QualificationRef
- --------------------------------
- 001 00101
- 001 00102
- 002 00201 -- Yay!
- 002 00202
- 003 00301
Category: Transact-SQL :: Article: 511