Anybody that has written applications for accounting or finance knows that Accountants love to “run the numbers.”  Often, they’ll simply add up a column of numbers to make sure that thee sum of the detail lines is equal to the subtotal or total (another way of saying this is "do the detail lines foot to the subtotal?").  Most of the time, this will not be a problem, like for example, when you're generating totals and subtotals with SQL or your reporting tool.

Of course, when a set of rounded detail quantities were created by an allocation and the total needs to foot back exactly to the allocated amount, this makes the situation interesting.  Having done programming support for finance and accounting for many years, I’ve solved this case programmatically many times.  We even had a utility program we called “Fudge Rounding” just for this purpose.

Then I had a need to do it in SQL (MS SQL Server 2005 specifically).  After thinking about it a few minutes, I came to the realization that it is not as simple as it sounds because there aren’t any built-in SQL functions that do this.

So let's start by setting up some data to use for this example:

DECLARE @Hdr TABLE
(
 key1  VARCHAR(20),
 Amount  Money
)
DECLARE @Dtl TABLE
(
 key1  VARCHAR(20),
 key2  VARCHAR(20),
 Weight  DECIMAL(6,2)
)
INSERT INTO @Hdr
          SELECT 'ABC' AS Key1, 100 As Amount
UNION ALL SELECT 'DEF', 501
UNION ALL SELECT 'GHI', 251
UNION ALL SELECT 'JKL', 151
INSERT INTO @Dtl (key1, key2, Weight)
          SELECT 'ABC' AS key1, '1' AS Key2, 33 AS Weight
UNION ALL SELECT 'ABC', '2', 33
UNION ALL SELECT 'ABC', '3', 33
UNION ALL SELECT 'DEF', '2', 25
UNION ALL SELECT 'DEF', '3', 34
UNION ALL SELECT 'DEF', '4', 0
UNION ALL SELECT 'GHI', '1', 100
UNION ALL SELECT 'GHI', '2', 100
UNION ALL SELECT 'GHI', '3', 50
UNION ALL SELECT 'JKL', '1', 0
UNION ALL SELECT 'JKL', '2', 50
UNION ALL SELECT 'JKL', '3', 50
UNION ALL SELECT 'JKL', '4', 50

The easy part is doing the allocation so I'll skip the explanation and just show the SQL and results.

SELECT d.*
     , h.Amount
     , ROUND(CAST(h.Amount * d.Weight / w.Weight AS Money), 0) As AllocAmt
 FROM @Dtl d
  INNER JOIN @Hdr h ON d.key1 = h.key1
  INNER JOIN (
              SELECT key1, SUM(Weight) AS Weight
               FROM @Dtl GROUP BY key1
             ) w ON d.key1 = w.key1

The following results are displayed.

In the case of DEF, the sum of the allocated amounts (212+289+0) matches exactly the amount that was allocated (501) to those rows.

However, for ABC, GHI and JKL, the sum of the allocated amounts does not match the total amount.

For ABC: 33+33+33 = 99 (instead of 100)

For GHI: 100+100+50 = 250 (instead of 251)

For JKL: 50+50+50 = 150 (instead of 151)

Now is where it gets interesting.

The normal process is to find the extra amount that was not allocated (=1 for ABC, GHI and JKL) and then add it to one of the rows.  Or another way to say it is that we should find the row we want to put it in and then sum up the allocated amount on the other rows, then subtract that from the total amount that was allocated, making that the value for the selected row.

If we're thinking in sets like we should be in SQL, the explanation above makes us think of two: 1) the set of rows to be "fudged" and 2) all the other rows.  This imples a UNION operator.  When I was thinking this up, it looked to me like it was going to get messy really quickly, so I decided CTEs might clean it up a bit.  We can rearrange the SQL presented earlier like this so that we have a starting basis.  Note that the semicolon in front of WITH seems to be required in the case where WITH follows the data setup SQL we provided earlier.

;WITH w AS (
 SELECT key1, SUM(Weight) AS Weight
 FROM @Dtl GROUP BY key1)
SELECT d.*
     , h.Amount
     , ROUND(CAST(h.Amount * d.Weight / w.Weight AS Money), 0) As AllocAmt
FROM @Dtl d
  INNER JOIN @Hdr h ON d.key1 = h.key1
  INNER JOIN w ON d.key1 = w.key1

You can run this yourself to convince yourself it produces the same result set.

Let's also make a decision on which row we'll allocate the leftover (unallocated) amount to.  For ease of understanding, we'll just choose the first row of each key1 value set.  This implies using a windowing function to PARTITION by key grouping, and then generate a sequence number.  So let's do that in another CTE:

;WITH w AS (
   SELECT key1, SUM(Weight) AS Weight
    FROM @Dtl GROUP BY key1),
  d AS (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY key1 ORDER BY key1, key2) AS SeqNo
      FROM @Dtl)
 SELECT d.*, h.Amount, ROUND(CAST(h.Amount * d.Weight / w.Weight AS Money), 0) As AllocAmt
  FROM d
    INNER JOIN @Hdr h ON d.key1 = h.key1
    INNER JOIN w ON d.key1 = w.key1

Now comes the fun part!  Once again, to make things a little cleaner we will push the allocation statement up into a CTE and at the same time create one more CTE that will calculate the sum of the allocated amounts excluding SeqNo 1.  Then we can UNION the two sets together, ordering by keys to make the results easy to see like this.

;WITH w AS (
  SELECT key1, SUM(Weight) AS Weight
  FROM @Dtl GROUP BY key1),
 d AS (
  SELECT *,ROW_NUMBER() OVER (PARTITION BY key1 ORDER BY key1, key2) AS SeqNo
  FROM @Dtl),
 a AS (
  SELECT d.key1, d.key2, d.Weight, d.SeqNo
   ,ROUND(CAST(Amount * d.Weight / w.Weight AS Money), 0) As AllocAmt
  FROM d
   INNER JOIN @hdr h ON h.key1 = d.key1
   INNER JOIN w ON h.key1 = w.key1),
 b AS (
 SELECT key1, SUM(AllocAmt) As AllocAmt
 FROM a WHERE SeqNo <> 1 GROUP BY key1)
SELECT a.key1, a.key2, a.Weight, a.AllocAmt
FROM a WHERE SeqNo <> 1
UNION ALL
SELECT a.key1, a.key2, a.Weight, h.Amount - ISNULL(b.AllocAmt, 0)
FROM a
LEFT OUTER JOIN b ON a.key1 = b.key1 
INNER JOIN @Hdr h ON h.key1 = a.key1
WHERE SeqNo = 1
ORDER BY key1, key2

The results now look like this where sum of all detail lines within the groups sum exactly to the total.  We have just fudge rounded!!

Note that the ISNULL around b.AllocAmt is needed for the case where there is only one row to be allocated to.

Unfortunately there remains one issue appearing in the result set, which has to do with our choice of assigning the leftover.  Look at the first JKL record where the weight is 0.  The leftover 1 was fudged to here – not good! 

The normal process in an allocation is not to select a somewhat arbitrary record like we have, but rather assign the leftover to the largest weighted record.  This is only a minor change to the PARTITION ORDER BY keys (use weight instead of key2).

Commented code and results appear below.

;WITH w AS (
  -- If weight is denormalized (no total on header) find the total
  SELECT key1, SUM(Weight) AS Weight
   FROM @Dtl GROUP BY key1),
  -- Assign a row number to each key1 group based on descending weight
  d AS (
   SELECT *,
     ROW_NUMBER() OVER (PARTITION BY key1 ORDER BY key1, Weight DESC) AS SeqNo
 FROM @Dtl),
-- Calculate the rounded, allocated amount
a AS (
  SELECT d.key1, d.key2, d.Weight, d.SeqNo
    ,ROUND(CAST(Amount * d.Weight / w.Weight AS Money), 0) AllocAmt
   FROM d INNER JOIN @hdr h ON h.key1 = d.key1 INNER JOIN w ON h.key1 = w.key1),
-- Calculate the total of all rows not "fudge" rounded
b AS (
 SELECT key1, SUM(AllocAmt) As AllocAmt
  FROM a WHERE SeqNo <> 1 GROUP BY key1)
-- Retrieve all but the "fudge" rounded rows
SELECT a.key1, a.key2, a.Weight, a.AllocAmt FROM a WHERE SeqNo <> 1
UNION ALL
-- Retrieve the "fudge" rounded rows
SELECT a.key1, a.key2, a.Weight, h.Amount - ISNULL(b.AllocAmt, 0)
 FROM a LEFT OUTER JOIN b ON a.key1 = b.key1 INNER JOIN @Hdr h ON h.key1 = a.key1
 WHERE SeqNo = 1 ORDER BY key1, key2

To summarize the process, we have:

• Created an allocation from a header record to rounded detail records.
• Broken the detail rows into two groups: 1) those that get assigned the directly allocated amount and 2) those that were assigned a "fudged" allocation that is the total allocated amount minus the sum of group 1).
• Grouple the two groups together with a UNION clause.
• Determine which record to "fudge" by identifying the one with the largest weight using ROW_NUMBER() and PARTITIONing on the weight column.

While not shown in this example, the above process can be done entirely with derived tables, however the result is quite messy.  The result using CTEs is much more readable. 

I hope you found this helpful and I hope to provide other useful examples in the near future.