# In a given series how to get max sum of 5 no and that should be less than 50. [Answered]RSS

Last post May 09, 2016 01:19 PM by eralper

• Rammi44

Member

12 Points

28 Posts

### In a given series how to get max sum of 5 no and that should be less than 50.

suppose i have a given series like 10,20,17,3,6,8,2,4.18,25= total 113. i need to split in to 3 categories each contain max sum of 50 and and each category max of 5 elements.

how to do it.

• eralper

Contributor

5981 Points

1411 Posts

### Re: In a given series how to get max sum of 5 no and that should be less than 50.

Hello Rammi44,

Please check following SQL While loop statement

```declare @series nvarchar(max) = '10,20,17,3,6,8,2,4,18,25'
create table ##t2(val int, cat int)

declare @max int, @catcnt int = 1, @i int = 1, @sum int = 0, @val int, @cntr int = 0

select * into ##t from dbo.split(@series,',') s

select @max = MAX(id) from ##t

while @i <= @max
begin
select @val = val from ##t where id = @i
set @cntr = @cntr + 1

if ( (@val + @sum) > 50 or @cntr = 5)
begin
select @catcnt = @catcnt + 1, @cntr = 0
insert into ##t2 select @val, @catcnt
end
else
begin
insert into ##t2 select @val, @catcnt
end

set @i = @i + 1
end
select * from ##t2
drop table ##t
drop table ##t2
```

Before you run the above script, you need the create the SQL Split string function on your database. It returns the splitted value besides the order of the string fragment

The result will be as follows

```val         cat
----------- -----------
10          1
20          1
17          1
3           1
6           2
8           2
2           2
4           2
18          2
25          3```

I hope it helps