SQL Server - reset current amount

Question

I am trying to get paging logic.

I have fields like:

RecordNo Lines 1 20 2 130 3 50 4 60 5 350 6 100 

Say my pages are 170 lines long.

I want to get the result:

 RecordNo Lines CumSum PageNo 1 20 20 1 2 130 150 1 3 50 50 2 (as cumulative sum 200 exceeds 170, reset to 0) 4 60 110 2 5 350 350 3 ((as cumulative sum 460 exceeds 170, reset to 0) 6 100 100 4 ((as cumulative sum 460 exceeds 170, reset to 0) 

I can do this with the cursor, but is there any way to achieve this only in SQL (s)?

Here is the ddl and sample data that the OP sends:

 CREATE TABLE PAGING (RECORDNO INT, LINES INT ); INSERT INTO PAGING VALUES(1,20); INSERT INTO PAGING VALUES(2,130); INSERT INTO PAGING VALUES(3,50); INSERT INTO PAGING VALUES(4,60); INSERT INTO PAGING VALUES(5,350); INSERT INTO PAGING VALUES(6,100); 

Update:

Zohar, Thanks for looking at this. The request worked perfectly with the data that I gave, but when I expanded the data, it does not give the correct result, since the page base does not move with an amount exceeding 170.

Here is the data I tried SQL with:

 INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (1, 20); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (2, 130); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (3, 50); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (4, 60); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (5, 350); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (6, 100); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (7, 20); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (8, 10); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (9, 20); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (10, 30); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (11, 5); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (12, 5); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (13, 5); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (14, 10); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (15, 205); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (16, 156); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (17, 5); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (18, 2); INSERT [dbo].[PAGING] ([RECORDNO], [LINES]) VALUES (19, 7); 

Answer 1

Update:
Here is a complete solution and a link to the script :

 WITH cte AS ( SELECT RECORDNO, LINES, SUM(LINES) OVER (ORDER BY RECORDNO) CumSum, SUM(LINES) OVER (ORDER BY RECORDNO) / 170 AS PageNumberBase FROM PAGING ) SELECT RECORDNO, LINES, SUM(LINES) OVER (PARTITION BY PageNumberBase ORDER BY RECORDNO) As CumSum, DENSE_RANK() OVER(ORDER BY PageNumberBase) As PageNumber FROM cte ORDER BY RECORDNO 

First version:

Using the dense_rank and cte functions I was able to produce something very close, just cumSum is not perfect.
Unfortunately, I do not have time to play with him, so I will leave him here with a link to sqlFiddle and hope that OP or someone else can complete the solution:

 WITH cte AS ( SELECT RECORDNO, LINES, SUM(LINES) OVER (ORDER BY RECORDNO) CumSum, SUM(LINES) OVER (ORDER BY RECORDNO) / 170 AS PageNumber FROM PAGING ) SELECT RECORDNO, LINES, PageNumber, CumSum - (170 * PageNumber) As CumSum, DENSE_RANK() OVER(ORDER BY PageNumber) FROM cte 

Here is the link to the violin