SQL Integer Division Remainder

Question

Here is my situation: Say I have 7 hats.

Month       Hats
1           7

In my select statement I need to divide this by 3 and I can't have partial hats . I want to have 3 records with the following values:

Week     Hats
1        2
2        2
3        3

With normal rounding I would end up

Week     Hats
1        2
2        2
3        2

I cant have this cause i just lost a hat. How do I give the remainder of the quotient to one record (I don't care what record)?

Answer 1

Interesting problem. You can do this using SQL-only. The idea is to allocate the number of hats as an integer. Then, add an additional hat to each week until you hit the total needed.

Here is a query that accomplishes this, for the data you have proposed:

with weeks as (
      select 1 as w union all select 2 union all select 3
     ),
     const as (
      select 7 as numhats,
             (select count(*) from weeks) as numweeks
     )
select w,
       ((numhats / numweeks) +
        (case when row_number() over (order by w) <= numhats % numweeks
              then 1
              else 0
         end)
       ) as hats
from weeks cross join
     const;

Note that this puts the extra hats on the first weeks rather than the last weeks. You could put them on the last weeks by using order by w desc instead of order by w.