Select the sum of the column without the cursor

Question

I have data like:

ID LENGTH_IN_CM 1 1.0 2 1.0 3 9.0 4 5.0 5 15.0 6 3.0 7 5.0 

I know that the page has a length of 20 cm and wants to calculate on which pages each element will be placed.

So, for example, elements with identifiers 1, 2, 3 and 4 will be on the first page (1.0 + 1.0 + 9.0 + 5.0 and 20.0), but 5 and 6 will be on the second and 7 on the third.

Can I calculate page numbers without using a cursor?

Answer 1

Well, I did it more for the task than because I definitely consider this a good idea. I tend to believe Aaron that the cursor might be more appropriate. Anyway:

 declare @Items table (ID int not null,LENGTH_IN_CM decimal(5,1) not null) insert into @Items(ID,LENGTH_IN_CM) values (1,1.0), (2,1.0), (3,9.0), (4,5.0), (5,15.0), (6,3.0), (7,6.0) ;With PossiblePages as ( select ID as MinID,ID as MaxID,LENGTH_IN_CM as TotalLength from @Items union all select MinID,MaxID + 1,CONVERT(decimal(5,1),TotalLength + LENGTH_IN_CM) from PossiblePages pp inner join @Items it on pp.MaxID + 1 = it.ID where TotalLength + LENGTH_IN_CM <= 20.0 ), LongPages as ( select MinID,MAX(MaxID) as MaxID,MAX(TotalLength) as TotalLength from PossiblePages group by MinID ), FinalPages as ( select MinID,MaxID,TotalLength from LongPages where MinID = 1 union all select lp.MinID,lp.MaxID,lp.TotalLength from LongPages lp inner join FinalPages fp on lp.MinID = fp.MaxID + 1 ), PageNumbers as ( select MinID,MaxID,ROW_NUMBER() OVER (ORDER BY MinID) as PageNo from FinalPages ) select * from PageNumbers 

Result:

 MinID MaxID PageNo ----------- ----------- -------------------- 1 4 1 5 6 2 7 7 3 

Which should be simple enough to join the source table if you want to assign a page number to each row.

PossiblePages calculates every possible page - for each line, it acts as if this line was the first on this page, and then finds out how many additional lines can be added to it, and the total length of this range of lines (these can be cleaner ways calculating this expression, not sure at the moment).

LongPages will then find the longest value that PossiblePages calculated for each start line number.

Finally, FinalPages starts on the first page (it should logically be what started with ID 1 - you can always enter a different calculation if you are not guaranteed to start with 1, and you need to find one earlier). Then the next page is the one that starts with the line identifier that exceeds the previous line.

You do not need PageNumbers , but, as I said, I was thinking about joining the source table.

---

And as predicted by the commentators, I don’t think this will work well - only on the sample I see at least 4 table scans to calculate this.

---

Bonus of Madness. This one only scans the table 3 times:

 ;With PageRows as ( select ID as MinID,ID as MaxID,LENGTH_IN_CM as TotalLength from @Items where ID=1 union all select MinID,MaxID + 1,CONVERT(decimal(5,1),TotalLength + LENGTH_IN_CM) from PageRows pr inner join @Items ir on pr.MaxID = ir.ID-1 where TotalLength + LENGTH_IN_CM <= 20.0 union all select ir.ID as MinID,ir.ID as MaxID,ir.LENGTH_IN_CM as TotalLength from PageRows pr inner join @Items ir on pr.MaxID = ir.ID-1 where TotalLength + LENGTH_IN_CM > 20.0 ), PageNumbers as ( select MinID,MAX(MaxID) as MaxID,ROW_NUMBER() OVER (ORDER BY MinID) as PageNo from PageRows group by MinID ) select * from PageNumbers 

Answer 2

That also worked. Tell me why these approaches do not satisfy your needs.

 declare @mytable as table(id int, LENGTH_IN_CM int) insert into @mytable values (1,1), (2,1), (3,9), (4,5), (5,15), (6,3), (7,6); Select t.id , (select floor(SUM(LENGTH_IN_CM)/20.0)+1 page from @myTable where id<=t.id) from @mytable t id Page ----------- --------------------------------------- 1 1 2 1 3 1 4 1 5 2 6 2 7 3 (7 row(s) affected)