SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

/* Data setup */

CREATE TABLE #Temp (ID int PRIMARY KEY, M int);

INSERT INTO #Temp (ID, M)
VALUES
(1, 1),    (5, 9),
(2, 3),    (6, 12),
(3, 5),    (7, 13),
(4, 8);
7 rows affected

/* Answer */

WITH M_info AS
    (SELECT  ID, M, (M - LAG(M, 1) OVER (ORDER BY ID)) AS Dif_Last_M
        FROM #Temp
    )
SELECT  ID, 
        M, 
        SUM(CASE WHEN Dif_Last_M > 1 THEN Dif_Last_M - 1 ELSE 0 END) OVER (ORDER BY ID) AS N
FROM    M_info;


ID
M
N


1
1
0

2
3
1

3
5
2

4
8
4

5
9
4

6
12
6

7
13
6


/* CTE component (M_Info) */

SELECT  ID, M, (M - LAG(M, 1) OVER (ORDER BY ID)) AS Dif_Last_M
        FROM #Temp
        


ID
M
Dif_Last_M


1
1
null

2
3
2

3
5
2

4
8
3

5
9
1

6
12
3

7
13
1


/* Also include values used in running total */

WITH M_info AS
    (SELECT  ID, M, (M - LAG(M, 1) OVER (ORDER BY ID)) AS Dif_Last_M
        FROM #Temp
    )
SELECT  ID, 
        M, 
        CASE WHEN Dif_Last_M > 1 THEN Dif_Last_M - 1 ELSE 0 END AS Value_to_Add,
        SUM(CASE WHEN Dif_Last_M > 1 THEN Dif_Last_M - 1 ELSE 0 END) OVER (ORDER BY ID) AS N
FROM    M_info;


ID
M
Value_to_Add
N


1
1
0
0

2
3
1
1

3
5
1
2

4
8
2
4

5
9
0
4

6
12
2
6

7
13
0
6

ID	M	Dif_Last_M
1	1	null
2	3	2
3	5	2
4	8	3
5	9	1
6	12	3
7	13	1