SQL aggregation for a smaller set of results

Question

I have a database for which I need to aggregate records into another smaller set. This result set should contain the difference between the maximum and minimum number of columns of the source records, where they make up a certain SUM, constant with interval C.

The constant C determines how the source records are aggregated, and no record in the result set ever exceeds it. Naturally, I have to run this in the order of the primary primary key.

To illustrate: the table has:

• [key]
• [a]
• [B]
• [minColumn]
• [maxColumn]
• [N]

... all are internal data types.

I am after a result set that has entries where MAX (maxColumn) is MIN (minColumn) for this group, so when their difference is summed, it is less than or equal to constant C.

In addition to the values ​​of MAX (maxColumn) and MIN (minColumn), before creating a new record in this result set, I also need the FIRST column column [a] and LAST record [b]. Finally, column N must be SUMmed for all source records in the group.

Is there an effective way to do this without cursors?

----- [Trivial example] -------------------------------------- --- -------------------

I am trying to group a somewhat complicated form of the current amount, constant C.

There is only one table, all columns are of type int and sample data

declare @t table (
  PK int primary key
    , int a, int b, int minColumn, int maxColumn, int N 
)

insert @t values (1,5,6,100,200,1000)
insert @t values (2,7,8,210,300,2000)
insert @t values (3,9,10,420,600,3000)
insert @t values (4,11,12,640,800,4000)

Thus, for:

key, a,   b, minColumn, maxColumn,    N
---------------------------------------
1,   5,   6,       100,       200, 1000 
2,   7,   8,       210,       300, 2000 
3,   9,  10,       420,       600, 3000 
4,   11, 12,       640,       800, 4000 

I need the result set to look like this: for constant C 210:

firstA | lastB | MIN_minColumn | MAX_maxColumn | SUM_N
5       8                  100             300    3000 
9       10                 420             600    3000 
11      12                 640             800    4000 

[Adding a bounty and sample as described below]

C = 381 2 :

firstA | lastB | MIN_minColumn | MAX_maxColumn | SUM_N
5            8             100             300    3000 
9           12             420             800    7000

---

, .. C say 1000 1 :

firstA | lastB | MIN_minColumn | MAX_maxColumn | SUM_N
5           12             100             800   10000

Answer 1

DECLARE @c int
SELECT @c = 210

SELECT MIN(a) firstA,
       MAX(b) lastB, 
       MIN(minColumn) MIN_minColumn, 
       MAX(maxColumn) MAX_maxColumn, 
       SUM(N) SUM_N
FROM @t t 
JOIN (SELECT key, floor(sum/@c) as rank
        FROM (SELECT key, 
                     (SELECT SUM(t2.maxColumn - t2.minColumn) 
                        FROM @t t2 
                       WHERE t2.key <= t1.key 
                    GROUP BY t1.key) as sum
               FROM @t t1) A
     ) B on B.key = t.key
GROUP BY B.rank

/*

Table A: for each key, calculating SUM[maxColumn-minColumn] of all keys below it.
Table B: for each key, using the sum in A, calculating a rank so that:
  sum = (rank + y)*@c where 0 <= y < 1. 
  ex: @c=210, rank(100) = 0, rank(200) = 0, rank(220) = 1, ...
finally grouping by rank, you'll have what you want.

*/

Answer 2

@c int

@c = 210

firstA = min (a), lastB = max (b), MIN_minColumn = min (minColumn), MAX_maxColumn = max (maxColumn), SUM_N = sum (N) @t minColumn <= @c

a, b, minColumn, maxColumn, N @t minColumn > @c

Answer 3

, , , , , HAVING. - :

SELECT groupingA, groupingB, MAX(a) - MIN(b)
FROM ...
GROUP BY groupingA, groupingB
HAVING (MAX(a) - MIN(b)) < C

..., max min, . ,