关于 SQL Server 2000 中计费查询的建议

Question

我需要一些处理查询的建议。 我可以在前端应用程序中处理这个问题，但是，由于设计原因，我必须在后端实现它。 我有以下

CREATE TABLE [dbo].[openitems](
    [id] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [type] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [date] [smalldatetime] NULL,
    [amount] [decimal](9, 2) NULL,
    [daysOpen] [smallint] NULL,
    [balance] [decimal](9, 2) NULL
) ON [PRIMARY]




insert into openitems values('A12399','INV','2008-12-05',491.96,123)

insert into openitems values('A12399','INV','2008-12-12',4911.37,116)

insert into openitems values('A12399','INV','2008-12-05',3457.69,109)

上面的表格包含客户的所有未结发票。 我需要从最旧的发票（表中的 daysOpen 列）开始对这些发票进行付款。 因此，如果我有 550.00 美元的付款，我将首先将其应用于 123 天的发票，即 491.96 美元 - 500 美元（剩下 8.04 美元应用于下一张发票......依此类推），然后更新该记录（余额表中的列）到 0.00 并移至下一个并应用剩余的。 这将是 4911.37 美元 - 8.04 美元，还剩下 4903.33 美元。 由于没有剩余余额可供应用，因此循环退出。

余额列现在应显示为

0
4903.33
3457.69

“注意：我需要对表中的所有客户（大约 10,000 个）执行此操作。 客户平均大约有 20 张未结发票。

谢谢

Answer 1

请考虑以下情况：

付款要么全额应用于余额，部分应用于余额，要么超额支付余额。

现在，想象一下，对于任何余额，我们都可以找到迄今为止发票的累积余额。 不要想象，让我们这样做：

create view cumulative_balance as
select a.*, 
  (select sum( balance ) 
  from openitems b 
  where b.id = a.id and b.type = a.type and a.daysOpen >= a.daysOpen)
  as cumulative_balance
from openitems a;

现在我们可以找到任何 id 和类型小于或等于付款的第一个累积余额，并将其以及 daysOpen 和累积余额存储在服务器变量中。

然后我们用该 id 和类型更新所有 openItems，其中 daysOpen <= 我们得到的值，将所有这些余额设置为零。

然后我们找到该 id 和类型的第一个非零余额，并将其余额设置为其余额 - （付款 - 我们存储的累积余额）。 如果多付，该余额将正确地为负数。

通过正确的查询，您将能够在一个语句中进行查找和首次更新。

有两个问题。 一是您无法确定两个或多个具有相同 id、类型和 daysOpen 的 alance 中哪个应该先支付。 在您的表中添加唯一 id 可以作为这些情况的决胜局。

其次是需要保存累积余额以便在第二次更新的查询中使用它。 如果您正确设计了表格，其中发票金额列未由付款更新，而付款列则为，这将解决您的问题。

更好的重构是有两张表，一张用于发票，一张用于付款：然后一个视图可以完成所有工作，将累积余额与累积付款进行比较，生成未付余额或超额付款的列表。

事实上，我正是为一家大型抵押贷款担保公司（其缩写为 FM）设计了这样一个系统。 它比你所拥有的要复杂一些，因为余额是根据许多金额和百分比的公式计算出来的，并且多个付款人（实际上，保险公司，这是针对已经违约的抵押贷款）必须以发票形式开具发票。根据其他规则，针对每项违约抵押贷款制定规定的顺序。

所有这些都是在视图中完成的，使用一个简短的（100 行左右）存储过程，该过程基本上完成了我上面概述的操作：使用一个视图，根据这些规则订购发票的账单，应用付款（在视图中），计算在什么日期向哪家保险公司开具发票的额外付款。 然后，存储过程仅生成当前日期的发票（可以再次使用视图将当前日期设置为任何日期以进行测试）。

（具有讽刺意味的是，我接受这份工作时承诺我会编写 C++；我编写的唯一 C++ 使用 Oracle 和 Sybase C API 将数据从 Oracle 系统传输到 Sybase 系统。）

Answer 2

这应该可以做到。 我已经声明了一个局部变量，但您可以将其作为存储过程的参数。 我还在表中添加了一个发票 ID 来唯一标识发票，因为 ID 和日期似乎并不唯一。

DECLARE
    @paid_amount DECIMAL(9, 2)

SET @paid_amount = 500

UPDATE
    OI
SET
    balance =
            CASE
                WHEN @paid_amount - SQ.running_total > balance THEN 0
                ELSE balance - (@paid_amount - SQ.running_total)
            END
FROM
    dbo.OpenItems OI
INNER JOIN (
    SELECT
        I1.id,
        I1.invoice_id,
        I1.date,
        ISNULL(SUM(I2.amount), 0) AS running_total
    FROM
        OpenItems I1
    LEFT OUTER JOIN OpenItems I2 ON
        I2.id = I1.id AND
        I2.type = 'INV' AND
        I2.daysopen > I1.daysopen
    GROUP BY
        I1.id,
        I1.invoice_id,
        I1.date
) AS SQ ON
    SQ.id = OI.id AND
    SQ.invoice_id = OI.invoice_id
WHERE
    @paid_amount > SQ.running_total

Answer 3

除非这是一次性的工作...

听起来这是业务逻辑并且属于应用程序的业务层。

Answer 4

您将必须使用几个游标和两个嵌套循环。 （这可能有点慢）

读取所有付款的一个 - 我假设客户，金额

然后为每个客户为未清项目创建另一个游标。

第一个循环将读取付款，直到完成。

在该循环中，为客户的未清项目打开一个新游标，按最旧的顺序排序。

循环遍历每个未清项目并按照您的描述应用付款，

然后获取下一笔付款。

重复直到不再付款。