I have a database for which I need to aggregate records into another smaller set. This result set should contain the difference between maximum and minumum of specific columns of the original records where they add up to certain SUM, a closed interval constant C.
The constant C determines how the original records are aggregated and no entry in the resulting set ever exceeds it. Naturally I am supposed to run this in natural primary key order..
To illustrate: table has:
- [key]
- [a]
- [b]
- [minColumn]
- [maxColumn]
- [N]
...all are int datatype.
I am after a result set that has entries where the MAX(maxColumn) - MIN(minColumn) for that group such that when their difference is summed up it is less or equal to constant C.
Apart from the MAX(maxColumn) and MIN(minColumn) value I also need the FIRST record column [a] and LAST record column [b] values before creating a new entry in this result set. Finally, the N column should be SUMmed for all original records in a group.
Is there an efficient way to do this without cursors?
-----[Trivial Sample]------------------------------------------------------------
I am attempting to group-by a slightly complicated form of a running sum, constant C.
There is only one table, columns are all 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 need the result set to look like, for a 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 discussed below]
For C = 381, It should 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 problem better.. and for a constant C say 1000 you would get 1 record in the result:
firstA | lastB | MIN_minColumn | MAX_maxColumn | SUM_N
5 12 100 800 10000