I've got a database that I have to aggregate records into another more compact set. This result set should retain the distinction between maximum and minumum of specific posts from the original records where they equal to certain SUM, a closed interval constant C.

The continual C determines the way the original records are aggregated with no entry within the resulting set ever surpasses it. Naturally I'm designed to run this in natural primary key order..

As one example of: table has:

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

...each one is int datatype.

I'm following a result set which has records in which the MAX(maxColumn) - MIN(minColumn) for your group so that when their difference is summed up it's less or comparable to constant C.

Aside from the MAX(maxColumn) and MIN(minColumn) value I additionally require the FIRST record column [a] and LAST record column [b] values before developing a new entry within this result set. Finally, the N column ought to be SUMmed for those original records inside a group.

Can there be a competent method of doing this without cursors?

-----[Trivial Sample]------------------------------------------------------------

I'm trying to group-with a slightly complicated type of a running sum, constant C.

There's just one table, posts are of int type 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 want the end result set to appear like, for any constant C of 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 the bounty and sample as talked about below]

For C = 381, It will contain 2 rows:

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

Hope this demonstrates the issue better.. as well as for a continuing C say 1000 you can get 1 record within the result:

firstA | lastB | MIN_minColumn | MAX_maxColumn | SUM_N
5           12             100             800   10000
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


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.


declare @c int

choose @c = 210

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

union all

choose a, b, minColumn, maxColumn, N from @t where minColumn > @c

I'm a little confused around the grouping logic for result you are attempting to create, but in the description of what you're searching for, I want a Getting clause. You need to have the ability to make a move like:

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

...to be able to remove the main difference involving the max and min values, once you have determined your grouping. Hope this really is useful