How to split a numeric field into smaller segments in Sql Server

Question

I have a table in SQL Server with two fields.

Total      Group
35645      24
12400      55
30000      41

I want to split each group into smaller segments of fixed size 7000, with the remainder of each group into the last segment. So, the output should look like below.

Segment  Total     Group
1        7000       24
2        7000       24
3        7000       24
4        7000       24
5        7000       24
6        645        24
1        7000       55
2        5400       55
1        7000       41
2        7000       41
3        7000       41
4        7000       41
5        2000       41

Answer 1

This should do it:

declare @t table (Total int,[Group] int)
insert into @t(Total,[Group]) values
(35645,24 ),
(12400,55 ),
(30000,41 )
;With Numbers as (
    select ROW_NUMBER() OVER (ORDER BY number)-1 n
    from master..spt_values
)
select
    n.n+1 as Segment,
    CASE WHEN (n.n+1)*7000 < t.Total THEN 7000
    ELSE t.Total - (n.n*7000) END as Total,
    t.[Group]
from
    @t t inner join
    Numbers n on n.n*7000 < t.Total

(If you already have a Numbers table you can eliminate that part. I'm using spt_values just as a table that I know has plenty of rows in it, so that the ROW_NUMBER() expression should generate all of the necessary numbers)

Results:

Segment              Total                Group
-------------------- -------------------- -----------
1                    7000                 24
2                    7000                 24
3                    7000                 24
4                    7000                 24
5                    7000                 24
6                    645                  24
1                    7000                 55
2                    5400                 55
1                    7000                 41
2                    7000                 41
3                    7000                 41
4                    7000                 41
5                    2000                 41