The goal for each quarterly column was to reduce the month (m in examples below) down to a zero or a one (false and true) then multiply that times the quantity and amount being totalled (value). Zero (false) when multiplied would produce a zero and therefore not add anything to the SUM. One (true) when multipled would produce the same number and be added to the SUM.

SELECT grouping_code

, SUM(value*(1-MIN(MAX(m-3,0),1)))

, SUM(value*(1-MIN(MAX(ABS(m-5)-1,0),1)))

, SUM(value*(1-MIN(MAX(ABS(mm-8)-1,0),1)))

, SUM(value*(1-MIN(MAX(ABS(mm-11)-1,0),1)))

FROM large_freakin_detail_table

WHERE criteria = 'whattheywerelookingfor'

GROUP BY grouping_code

So you are probably wondering what the blue heck all that SUM MIN MAX ABS crap is doing. Sometimes it is best to look at results. Assume the left-most column is the month (1 through 12) and see how that number is transformed into a true (1) or false (0) through the magic of math.

SUM(value*(1-MIN(MAX(m-3,0),1)))

m-3 MAX MIN 1-r

1 -2 0 0 1

2 -1 0 0 1

3 0 0 0 1

4 1 1 1 0

5 2 2 1 0

6 3 3 1 0

7 4 4 1 0

8 5 5 1 0

9 6 6 1 0

10 7 7 1 0

11 8 8 1 0

12 9 9 1 0

The first quarter is simple: subtract 3 (March) then take the highest of that value or zero. The 0 is higher than -2, -1 and 0, but not higher than 1, 2, etc. Now we have a zero in months 1, 2 and 3 (1st quarter). By looking for the lowest value between 1 and the last result you will get a 1 for 1 or more but the zeroes will stay 0. Subtract the result from 1 (0 = 1, 1 = 0) and now you have the true/false value to multiply against an amount to either zero it or not before adding it to the SUM. Tricky, eh?

SUM(value*(1-MIN(MAX(ABS(m-5)-1,0),1)))

# m-5 ABS r-1 MAX MIN 1-r

1 -4 4 3 3 1 0

2 -3 3 2 2 1 0

3 -2 2 1 1 1 0

4 -1 1 0 0 0 1

5 0 0 -1 0 0 1

6 1 1 0 0 0 1

7 2 2 1 1 1 0

8 3 3 2 2 1 0

9 4 4 3 3 1 0

10 5 5 4 4 1 0

11 6 6 5 5 1 0

12 7 7 6 6 1 0

The remaining quarters are more difficult but by using the absolute value (the positive value) you can eventually get the number you are looking for.

SUM(amount*(1-MIN(MAX(ABS(mm-8)-1,0),1)))

# m-8 ABS r-1 MAX MIN 1-r

1 -7 7 6 6 1 0

2 -6 6 5 5 1 0

3 -5 5 4 4 1 0

4 -4 4 3 3 1 0

5 -3 3 2 2 1 0

6 -2 2 1 1 1 0

7 -1 1 0 0 0 1

8 0 0 -1 0 0 1

9 1 1 0 0 0 1

10 2 2 1 1 1 0

11 3 3 2 2 1 0

12 4 4 3 3 1 0

This query used one sweep through the gigantic table and one sort for the group by. Grouping on month would have added an additional sort so that was not an option.

## No comments:

Post a Comment