I need to create an export process that aggregates our records based on common characteristics, but the aggregation also much have a total cost that is less than an arbitrary amount assigned by a variable. For example, suppose that you have:
1 Blue $1
2 Blue $1
3 Red $1
4 Red $2
And the limit is $2. The desired output is:
1 Blue $2
2 Red $2
3 Red $1
Using the standard COLOR, MAX(Cost) GROUP BY COLOR type of query will yield the first line, but the second line of the output above will be $3 and the last line will be absent.
Suppose further that the totalcost is actually a function of unitcost and quantity. So, the supplied data will really be:
1 Blue 2 $2
2 Blue 3 $2
3 Red 5 $4
4 Red 6 $4
Suppose the limit is $10. The desired output is:
1 Blue 5 $2 $10
2 Red 2 $4 $8
3 Red 2 $4 $8
4 Red 2 $4 $8
5 Red 2 $4 $8
6 Red 2 $4 $8
7 Red 1 $4 $4
Any suggestions on an elegant way to automate this? I need my data "lumpy" but not too "lumpy".
Thanks!