Generate serial number for each consecutive set of numbers

In this post, we are going to generate serial number for each consecutive set of numbers in a given column. We have a table tbl_Donation with columns EmpId and DonationYear. For each consecutive set of years of each employee, we need to generate serial numbers. If employee “X” donated in 2006, 2007 and 2008 years consecutively and then in 2010 and 2011, we have to assign 1 for 2005, 2 for 2006, 3 for 2007 followed by 1 for 2010 and 2 for 2011. We need to generate incremental series for each consecutive set of years but as and when a gap occurs between donation years, series has to be restarted with 1.

PARTITION BY clause in a ROW_NUMBER() window function could be able to generate the required output, if we had same number along with donation years for each consecutive set of years for all employees. Initially, we don’t have any such kind of value in the table but we can generate same number for each set of consecutive years for all employees, then this column can be used inside a PARTITION BY clause to generate the required serial number series.

Lets have a look on the column details of tbl_Donation table, before we proceed:

1. EmpId – Id of Employee who makes the donation
2. DonationYear – Year in which employee makes the donation

We can use below script to create a dummy table with sample data.

SET NOCOUNT ON;
IF(OBJECT_ID('tempdb..#tbl_Donation') IS NOT NULL)
DROP TABLE #tbl_Donation

CREATE TABLE #tbl_Donation
(
EmpId INT NOT NULL,
DonationYear INT NOT NULL
)

--Insert dummy data in table
INSERT INTO #tbl_Donation
(EmpId, DonationYear)

SELECT DT_Emp.EmpId, DT_Years.DonationYear FROM
(
SELECT 1 AS EmpId UNION ALL SELECT 2 UNION ALL SELECT 3
)DT_Emp
CROSS JOIN
(SELECT 2006 AS DonationYear UNION ALL SELECT 2007 UNION ALL SELECT 2008 UNION ALL SELECT 2011 UNION ALL SELECT 2015 UNION ALL SELECT 2016)DT_Years

In above script, we have three dummy employee ids (1, 2 and 3) with few dummy donation year values. We have cross joined the employee and donation year values to generate donation years for all employees. Table tbl_Donation should look like below now:

SELECT * FROM #tbl_Donation

Required output from above table is as below;

Get the required output in set based approach:

We can achieve the required output using a while loop or recursive CTE (it is also a kind of loop) or even using a cursor. As we know that, SQL Server is optimized for set based operations, we are going to achieve this task in a set based approach. I always try to follow set based approach rather than a RBAR (Row by agonizing row i.e. Loop) approach.

Set based approach uses joins and sub-queries rather than loops and recursions. We are using below set based query to extract the required output in a set based approach:

;WITH CTE AS
(
 SELECT EmpId, DonationYear, DonationYearPrev,
 CASE WHEN DonationYear - DonationYearPrev <> 1 THEN DonationYear ELSE DonationYear - DonationYearPrev END AS DiffAndPrev
 FROM
 (
 SELECT EmpId, DonationYear, LAG(DonationYear, 1, 0) OVER(ORDER BY EmpId, DonationYear) AS DonationYearPrev
 FROM #tbl_Donation
 )DT
)

SELECT EmpId, CTE1.DonationYear,
ROW_NUMBER() OVER(PARTITION BY EmpId, OA1.ValueToPartition ORDER BY OA1.ValueToPartition) AS Seq
FROM CTE AS CTE1
OUTER APPLY
(
 SELECT TOP 1 DiffAndPrev AS ValueToPartition
 FROM CTE AS CTE2
 WHERE CTE2.EmpId = CTE1.EmpId AND CTE2.EmpId = CTE1.EmpId AND CTE2.DonationYear <= CTE1.DonationYear
 ORDER BY DiffAndPrev DESC
)OA1

Discuss the trick

Lets discuss the process step by step:

1. Extract the previous year value with each row based on employee id as “DonationYearPrev”.

   SELECT EmpId, DonationYear, LAG(DonationYear, 1, 0) OVER(ORDER BY EmpId, DonationYear) AS DonationYearPrev
   FROM #tbl_Donation

2. If difference of current year and previous year is 1 (which means, the current row year is the next year of previous row year), put the DiffAndPrev column value as 1 else put the current row’s donation year value in the DiffAndPrev column in upper table inside CTE.

   CASE WHEN DonationYear - DonationYearPrev <> 1 THEN DonationYear ELSE DonationYear - DonationYearPrev END AS DiffAndPrev

   

3. In outer apply, we get the value of column ValueToPartition from column DiffAndPrev based on employee id where donation year is less than or equal to current row donation year ordered by DiffAndPrev in descending way. As we have taken either 1 or previous year value in step 2, we would get previous year value (which is not 1) from DiffAndPrev column for all 1’s of column DiffAndPrev.

   SELECT TOP 1 DiffAndPrev AS ValueToPartition
   FROM CTE AS CTE2
   WHERE CTE2.EmpId = CTE1.EmpId AND CTE2.EmpId = CTE1.EmpId AND CTE2.DonationYear <= CTE1.DonationYear
   ORDER BY DiffAndPrev DESC

   To explain this point, I have manipulated the final query (used below CTE) as below

   SELECT EmpId, CTE1.DonationYear, DiffAndPrev, OA1.ValueToPartition
   FROM CTE AS CTE1
   OUTER APPLY
   (
    SELECT TOP 1 DiffAndPrev AS ValueToPartition
    FROM CTE AS CTE2
    WHERE CTE2.EmpId = CTE1.EmpId AND CTE2.EmpId = CTE1.EmpId AND CTE2.DonationYear <= CTE1.DonationYear
    ORDER BY DiffAndPrev DESC
   )OA1

   Have a look on the output of the manipulated query:

   

4. Finally, we use column ValueToPartition in PARTITION BY clause with ROW_NUMBER() window function to generate the required serial numbers.

   ROW_NUMBER() OVER(PARTITION BY EmpId, OA1.PrevYear ORDER BY OA1.PrevYear) AS Seq

Hope, you have enjoyed this reading, please post your comments and queries (if any) below the post. Don’t forget to rate and share this blog post.