从日期范围内返回按比例分配的金额

我能够找到一个脚本，该脚本给定了开始和结束日期的范围，它将根据日期范围创建新行。我遇到的问题是，对于每条记录，我都有一个 AMOUNT 字段，我需要在整个日期范围内正确按比例分配。

CREATE TABLE #TempData (Company VARCHAR(6), InvoiceDate DATE, StartPeriod DATE, EndPeriod DATE, SchoolDistrict VARCHAR(100), Amount NUMERIC(10,2))
INSERT INTO #TempData (Company,InvoiceDate,StartPeriod,EndPeriod,SchoolDistrict,Amount)
SELECT '000123','1/1/2016','12/1/2015','12/31/2015','School District 123',140 UNION ALL
SELECT '000123','12/1/2016','6/15/2015','11/30/2015','School District 123',500 
;WITH Recurse AS (
SELECT Company,InvoiceDate, StartPeriod
,CAST(DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,StartPeriod)+1,0)) AS DATE) EOM,EndPeriod
,SchoolDistrict,Amount
FROM #TempData
UNION ALL
SELECT Company,InvoiceDate
,CAST(DATEADD(MONTH,DATEDIFF(MONTH,0,StartPeriod)+1,0) AS DATE) StartPeriod
,CAST(DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,StartPeriod)+2,0)) AS DATE)
,EndPeriod
,SchoolDistrict,Amount
FROM Recurse
WHERE EOM<EndPeriod
)
SELECT Company,InvoiceDate,StartPeriod
,CASE WHEN EndPeriod<EOM THEN EndPeriod ELSE EOM END EndPeriod
,SchoolDistrict,Amount
FROM Recurse
DROP TABLE TempData

我的输出如下所示：

Company InvoiceDate StartPeriod EndPeriod  SchoolDistrict       Amount
000123  2016-01-01  2015-12-01  2015-12-31 School District 123  140.00
000123  2016-12-01  2015-06-15  2015-06-30 School District 123  500.00
000123  2016-12-01  2015-07-01  2015-07-31 School District 123  500.00
000123  2016-12-01  2015-08-01  2015-08-31 School District 123  500.00
000123  2016-12-01  2015-09-01  2015-09-30 School District 123  500.00
000123  2016-12-01  2015-10-01  2015-10-31 School District 123  500.00
000123  2016-12-01  2015-11-01  2015-11-30 School District 123  500.00

至于第一条记录返回，无需按比例分配，

因为它只有 1 个月，但其他记录，我需要帮助了解如何在返回的 500 条记录中正确按比例分配 6 个金额。

注意更新：在整月是平均分配时，任何不是完整周期的开始期和/或结束期月份都会获得部分按比例分配。

下面是从原始输入日期和金额派生的表达式链。您可以轻松地将其输入到Recurse方法中，尽管我推荐其他一种方法来生成月份，例如使用数字表，特别是如果日期可以跨越多年。

对于部分月份，它根据该月涵盖的天数计算分数。除数是该月的总天数。有时会计师将一个月视为 30 天，因此您必须决定这是否合适。

全部金额分为整个月份，无论长度如何，加权均等，加上按各自月份的各自比例加权的两个部分。首先计算整月金额，然后将结果四舍五入;部分月份取决于该计算，并注意我在最后关于四舍五入到便士的后果的评论。最终结果需要注意正确分配最后一分钱，以便总和正确。

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
),
...

根据这些值，您可以确定在创建所有额外行后最终应以最终结果结束的金额。此表达式将通过合并原始查询来解决问题。（由于SQL Fiddle已经关闭，我无法测试任何内容：）

... /* 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

我添加/别名了RangeStart和RangeEnd值，以避免与您在临时表和输出查询中使用的StartPeriod和EndPeriod混淆。范围值表示整个跨度的开始和结束，周期值是分解各个周期的计算值。按照您认为合适的方式进行调整。

编辑#1：我意识到我没有处理过开始和结束在同一个月内的情况：也许有一种更干净的方法来完成整个事情。我只是最终将WholeMonths表达式清空以避免可能的除以零。末尾的 case 表达式捕获此条件，并仅返回原始Amount值。虽然你可能不必担心处理开始和结束日期被颠倒，但我继续用同一个< 1测试将它们绑在一起。

编辑#2：一旦我有一个地方可以尝试这个，你的测试用例显示四舍五入损失了一分钱，并且被最终的部分月份计算所拾取，即使它实际上是整个月份中的一个。所以我不得不调整以寻找没有最终部分月份的情况。那是在Expr4.我还发现了您注意到的几个小语法错误。

递归查询允许按顺序查看月份，并稍微简化了逻辑。定位点始终是开始月份，因此最后一个月的逻辑都不适用，查询的另一半也是如此。如果你最终用对数字表的常规连接来切换它，你会想改用这样的表达式：

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

编辑#3：另请注意，在处理四舍五入会使结果扭曲的非常小的金额时，此方法不合适。例子：

$0.13 除以 1 月 2 日到 12 月 1 日得到 [.01， .01， .01， .01， .01， .01， .01， .01， .01， .01， .02]$0.08 除以 1 月 2 日到 12 月 1 日得到 [.01， .01， .01， .01， .01， .01， .01， .01， .01， .01， -.03]$0.08 除以 1 月 31 日至 12 月 31 日得到 [-.03， .01， .01， .01， .01， .01， .01， .01， .01， .01， .01]$0.05 除以 1 月 31 日至 11 月 30 日得到 [.05， .00， .00， .00， .00， .00， .00， .00， .00， .00， .00]$0.05 除以 1 月 31 日至 12 月 1 日得到 [.00， .00， .00， .00， .00， .00， .00， .00， .00， .00， .05]$0.30 除以 1 月 2 日到 3 月 1 日得到 [.15， .15， .00]

这是一个有趣的问题，因为它既需要扩展行数，也需要在查询中检测和纠正舍入问题。

首先，需要一些繁琐的日期计算来计算每个月有多少天属于开始期和结束期。

然后按简单比例计算每个月的估算值，但舍入误差意味着这些估算值的总和不等于发票总金额。然后使用窗口函数计算总舍入误差，以便可以调整最后一笔付款。

顺便说一句，我建议使用具有简单"数字"视图的连接，而不是使用递归 CTE 为每个月生成一行。有关详细信息，请参阅有关数字表的问题

-- I use the #tempdata table mentioned in the question
; WITH numbers AS ( -- A fast way to get a sequence of integers starting at 0
    SELECT TOP(10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 as n
    FROM sys.all_columns a CROSS JOIN sys.all_columns b
),
data_with_pk AS ( -- Add a primary key so that we know how to sort output
    SELECT ROW_NUMBER() OVER (ORDER BY company, invoicedate) AS InvoiceId, *
    FROM #tempdata
),
step1 AS ( -- Calc first and last day of each month in which payment is due
    SELECT data_with_pk.*,
        CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, StartPeriod) + numbers.n, 0) 
             AS DATE) AS StartOfMonth,
        CAST(DATEADD(DAY, -1,  
                     DATEADD(MONTH, DATEDIFF(MONTH,0,StartPeriod) + numbers.n + 1, 0)) 
             AS DATE) AS EndOfMonth
    FROM data_with_pk
    -- This join is a simpler way to generate multiple rows than using a recursive CTE
    JOIN numbers ON numbers.n <= DATEDIFF(MONTH, StartPeriod, EndPeriod)
),
step2 AS ( -- Calc block of days in each month which fall within whole period 
    SELECT *,
        CASE WHEN StartPeriod > StartOfMonth THEN StartPeriod ELSE StartOfMonth END 
            AS StartOfBlock,
        CASE WHEN EndPeriod < EndOfMonth THEN EndPeriod ELSE EndOfMonth END 
            AS EndOfBlock
    FROM step1
), 
step3 AS ( -- Whole months count as 30 days for purposes of calculated proportions 
    SELECT *, 
        CASE WHEN StartOfBlock = StartOfMonth AND EndOfBlock = EndOfMonth 
             THEN 30
             ELSE DATEDIFF(DAY, StartOfBlock, EndOfBlock) + 1 END AS DaysInBlock
    FROM step2
), 
step3b AS (
    SELECT *,
        SUM(DaysInBlock) OVER (PARTITION BY InvoiceId) AS DaysInPeriod
    FROM step3
),
step4 AS ( -- Calc proportion of whole amount due in this block
    SELECT *, 
        CAST(Amount * DaysInBlock / DaysInPeriod AS NUMERIC(10,2)) AS Estimate
    FROM step3b
), 
step5 AS ( -- Calc running total of estimates
    SELECT *, 
      SUM(Estimate) OVER (PARTITION BY InvoiceId ORDER BY EndOfBlock) AS RunningEstimate
    FROM step4
), 
step6 AS ( -- Adjust last estimate to ensure final Prorata total is equal to Amount
    SELECT *, 
        CASE WHEN EndOfBlock = EndPeriod 
             THEN Estimate + amount - RunningEstimate 
             ELSE Estimate end AS Prorata
    FROM step5
),
step7 AS ( -- Just for illustration to prove that payments sum to the Invoice Amount
    SELECT *, 
        SUM(Prorata) OVER (PARTITION BY InvoiceId ORDER BY EndOfBlock) AS RunningProrata
    FROM step6
)
SELECT InvoiceId, InvoiceDate, StartPeriod, EndPeriod, Amount, DaysInBlock, EndOfBlock, 
    Estimate, RunningEstimate, Prorata, RunningProrata
FROM step7
ORDER BY InvoiceId, EndOfBlock

您可以看到下面结果集中的"估算"和"运行估算"列最终为 0.01 USD，但在"按比例"列中进行了更正。

+-----------+-------------+-------------+------------+--------+-------------+------------+----------+-----------------+---------+----------------+
| InvoiceId | InvoiceDate | StartPeriod | EndPeriod  | Amount | DaysInBlock | EndOfBlock | Estimate | RunningEstimate | Prorata | RunningProrata |
+-----------+-------------+-------------+------------+--------+-------------+------------+----------+-----------------+---------+----------------+
|         1 | 2016-01-01  | 2015-12-01  | 2015-12-31 | 140.00 |          30 | 2015-12-31 | 140.00   | 140.00          | 140.00  | 140.00         |
|         2 | 2016-12-01  | 2015-06-15  | 2015-11-30 | 500.00 |          16 | 2015-06-30 | 48.19    | 48.19           | 48.19   | 48.19          |
|         2 | 2016-12-01  | 2015-06-15  | 2015-11-30 | 500.00 |          30 | 2015-07-31 | 90.36    | 138.55          | 90.36   | 138.55         |
|         2 | 2016-12-01  | 2015-06-15  | 2015-11-30 | 500.00 |          30 | 2015-08-31 | 90.36    | 228.91          | 90.36   | 228.91         |
|         2 | 2016-12-01  | 2015-06-15  | 2015-11-30 | 500.00 |          30 | 2015-09-30 | 90.36    | 319.27          | 90.36   | 319.27         |
|         2 | 2016-12-01  | 2015-06-15  | 2015-11-30 | 500.00 |          30 | 2015-10-31 | 90.36    | 409.63          | 90.36   | 409.63         |
|         2 | 2016-12-01  | 2015-06-15  | 2015-11-30 | 500.00 |          30 | 2015-11-30 | 90.36    | 499.99          | 90.37   | 500.00         |
+-----------+-------------+-------------+------------+--------+-------------+------------+----------+-----------------+---------+----------------+

相关内容

最新更新

热门标签：