Sequencing records by year

Below is the simple query to detect how many time record is entered in database within one year. Sequence number is generated for the records for the same year from 1,2... and for next year again from 1,2.... and soon.

/* Creating table for entries */

CREATE TABLE #entries (RecordId INT IDENTITY(1,1), FinancialYear INT)

INSERT INTO #entries (FinancialYear) VALUES (2011),(2011),(2011),(2012), (2012), (2012), (2012),(2012), (2013), (2013), (2013)

/*Get all data of entry table to temp table with two aditional columns*/

SELECT *, CAST(0 AS INT) MatchYear, CAST(0 AS INT) EntryId INTO #temp FROM #entries

DECLARE @id INT = 1

UPDATE #temp

SET #temp.MatchYear = a.FinancialYear

FROM #entries a

WHERE #temp.RecordId = a.RecordId + 1

UPDATE #temp

SET EntryId = @id,

@id = @id + CASE WHEN FinancialYear = MatchYear THEN 1 ELSE -(@id-1) END

SELECT RecordId, FinancialYear, EntryId FROM #temp

DROP TABLE #entries, #temp

OUTPUT:-

RecordId    FinancialYear EntryId

----------- ------------- -----------

1           2011          1

2           2011          2

3           2011          3

4           2012          1

5           2012          2

6           2012          3

7           2012          4

8           2012          5

9           2013          1

10          2013          2

11          2013          3