Here is a chain of expression derived from the original input dates and amount. You can readily feed this into your Recurse method although I recommend one of the other methods for generating the months like using a numbers table, especially if the dates can range over many years.
For the partial months it calculates a fraction based on the number of days covered in that month. The divisor is the total number of days in that month. Sometimes accountants treat a month as having 30 days so you'll have to decide if this is appropriate.
The full amount is split across the full months, weighted equally regardless of length, plus the two partials weighted by their individual proportions of their respective months. The full month amount is computed first and that result is rounded; the partial months depend on that calculation and note my comment at the end regarding the consequences of rounding to the penny. The final results need to take some care to distribute the last penny correctly so that the sum is correct.
with Expr1 as (
select *,
StartPeriod as RangeStart, EndPeriod as RangeEnd,
case when datediff(month, StartPeriod, EndPeriod) < 1 then null else
datediff(month, StartPeriod, EndPeriod) + 1
- case when datepart(day, StartPeriod) <> 1
then 1 else 0 end
- case when month(EndPeriod) = month(dateadd(day, 1, EndPeriod))
then 1 else 0 end
end as WholeMonths,
case when datepart(day, StartPeriod) <> 1
then 1 else 0 end as IsPartialStart,
case when month(EndPeriod) = month(dateadd(day, 1, EndPeriod))
then 1 else 0 end as IsPartialEnd,
datepart(day, StartPeriod) as StartPartialComplement,
datepart(day, EndPeriod) as EndPartialOffset,
datepart(day,
dateadd(day, -1, dateadd(month, datediff(month, 0, StartPeriod) + 1, 0))
) as StartPartialDaysInMonth,
datepart(day,
dateadd(day, -1, dateadd(month, datediff(month, 0, EndPeriod) + 1, 0))
) as EndPartialDaysInMonth
from #TempData
),
Expr2 as (
select *,
case when IsPartialStart = 1
then StartPartialDaysInMonth - StartPartialComplement + 1
else 0 end as StartPartialDays,
case when IsPartialEnd = 1
then EndPartialOffset else 0 end as EndPartialDays
from Expr1
),
Expr3 as (
select *,
cast(round(Amount / (
WholeMonths
+ StartPartialDays / cast(StartPartialDaysInMonth as float)
+ EndPartialDays / cast(EndPartialDaysInMonth as float)
), 2) as numeric(10, 2)) as WholeMonthAllocation,
StartPartialDays / cast(StartPartialDaysInMonth as float) as StartPartialFraction,
EndPartialDays / cast(EndPartialDaysInMonth as float) as EndPartialFraction
from Expr2
),
Expr4 as (
select *,
cast(case when IsPartialEnd = 0
then Amount - WholeMonthAllocation * WholeMonths
else StartPartialFraction * WholeMonthAllocation
end as numeric(10, 2)) as StartPartialAmount,
cast(case when IsPartialEnd = 0 then 0
else Amount
- WholeMonthAllocation * WholeMonths
- StartPartialFraction * WholeMonthAllocation
end as numeric(10, 2)) as EndPartialAmount
from Expr3
),
...
From those values you can determine which amount should end up in the final result after you've created all the extra rows. This expression will do the trick by incorporating your original query. (Since SQL Fiddle has been down I haven't been able to test any of this:)
... /* all of the above */
Recurse AS (
SELECT
RangeStart, RangeEnd, IsPartialStart, IsPartialEnd,
StartPartialAmount, EndPartialAmount, WholeMonthAllocation,
Company, InvoiceDate, StartPeriod,
CAST(DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,StartPeriod)+1,0)) AS DATE) EOM,
EndPeriod, SchoolDistrict,
case
when datediff(month, RangeStart, RangeEnd) = 0 then Amount
when IsPartialStart = 1 then StartPartialAmount
else WholeMonthAllocation
end as Amount
FROM Expr4
UNION ALL
SELECT
RangeStart, RangeEnd, IsPartialStart, IsPartialEnd,
StartPartialAmount, EndPartialAmount, WholeMonthAllocation,
Company, InvoiceDate,
CAST(DATEADD(MONTH,DATEDIFF(MONTH,0,StartPeriod)+1,0) AS DATE) AS StartPeriod,
CAST(DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,StartPeriod)+2,0)) AS DATE) EOM,
EndPeriod, SchoolDistrict,
case
-- final month is when StartPeriod is one month before RangeEnd.
-- remember this is recursive!
when IsPartialEnd = 1 and datediff(month, StartPeriod, RangeEnd) = 1
then EndPartialAmount
else WholeMonthAllocation
end as Amount
FROM Recurse
WHERE EOM < EndPeriod
)
SELECT
Company, InvoiceDate, StartPeriod,
CASE WHEN EndPeriod < EOM THEN EndPeriod ELSE EOM END EndPeriod,
SchoolDistrict, Amount
FROM Recurse
I've added/aliased RangeStart and RangeEnd values to avoid confusion with StartPeriod and EndPeriod which you're using in both your temp table and output query. The Range- values represent the start and end of the full span and the Period- values are the computed values that break out the individual periods. Adapt as you see fit.
Edit #1: I realized that I had not handled the case where start and end fall in the same month: perhaps there's a cleaner way to do this whole thing. I just ended up nulling the WholeMonths expression to avoid a possible divide by zero. The case expression at the end catches this condition and just returns the original Amount value. Although you probably don't have to worry about dealing with start and end dates getting reversed I went ahead and roped them all together with the same < 1 test.
Edit #2: Once I had a place to try this out your test case showed that the rounding was losing a penny and was getting picked up by the final partial month calculation even when it was actually one of the whole months. So I had to adjust to look for the case where there is no final partial month. That's in Expr4. I also spotted several of the minor syntax errors that you noted.
The recursive query allows for seeing the months in order and simplifies the logic a little bit. The anchor is always going to be the start month and so none of the final month logic applies and similarly for the other half of the query. If you end up switching this out with a regular join against a numbers table you'd want to use an expression like this instead:
case
when datediff(month, RangeStart, RangeEnd) = 0
then Amount
when IsPartialStart = 1 and is first month...
then StartPartialAmount
when IsPartialEnd = 1 and is final month...
then EndPartialAmount
else WholeMonthAllocation
end as Amount
Edit #3: Also be aware that this method is not appropriate when dealing with very small amounts where the rounding is going to skew the results. Examples:
$0.13 divided January 02 to December 01 gives [.01, .01, .01, .01, .01, .01, .01, .01, .01, .01, .01, .02]
$0.08 divided January 02 to December 01 gives [.01, .01, .01, .01, .01, .01, .01, .01, .01, .01, .01, -.03]
$0.08 divided January 31 to December 31 gives [-.03, .01, .01, .01, .01, .01, .01, .01, .01, .01, .01, .01]
$0.05 divided January 31 to November 30 gives [.05, .00, .00, .00, .00, .00, .00, .00, .00, .00, .00]
$0.05 divided January 31 to December 01 gives [.00, .00, .00, .00, .00, .00, .00, .00, .00, .00, .00, .05]
$0.30 divided January 02 to March 1 gives [.15, .15, .00]
