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 … More