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:
...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?
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)
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 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. */
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
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