Sequencing records by year and there Running Total for records in the same year

Query is simple and understandable, no group or any aggregate function is used. Just simple insert and update statements can do the task.

/* Creating temporary table */

CREATE TABLE #entries (RecordId INT IDENTITY(1,1), FinancialYear INT, Amount INT, Total INT DEFAULT 0)

/* Inserting dummy data to table */

INSERT INTO #entries (FinancialYear, Amount) VALUES (2011, 5),(2011, 10),(2011, 15),(2012, 10), (2012, 20), (2012, 30),

(2012, 50),(2012, 50), (2013, 100), (2013, 150), (2013, 200)

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

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

DECLARE @id INT = 0, @total INT = 0, @nextAmt INT = 0, @firstYear INT, @overAllTotal INT = 0

UPDATE #temp

SET #temp.MatchYear = a.FinancialYear

FROM #entries a

WHERE #temp.RecordId = a.RecordId + 1

SELECT TOP 1 @firstYear = FinancialYear FROM #temp

UPDATE #temp SET MatchYear = @firstYear WHERE MatchYear = 0

UPDATE #temp

SET Total = CASE WHEN FinancialYear = MatchYear THEN 0 ELSE Amount END

UPDATE #temp

SET EntryId = @id,

Total = @total,

OverAllTotal = @overAllTotal,

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

@total = CASE WHEN FinancialYear = MatchYear

THEN @total + Amount ELSE Amount END,

@overAllTotal += Amount

SELECT RecordId, FinancialYear, MatchYear, EntryId, Amount, Total, OverAllTotal FROM #temp

DROP TABLE #entries, #temp

Below is the screen shot :-

How to