sql-server - 按比例在帐户之间拆分 SQL 2008

Question

有人要求我创建一份报告，在多个账户之间拆分付款。付款来自主账户。然后我需要根据引用余额按比例拆分它。

示例数据:

CREATE TABLE [dbo].[tblProRataSplit](
    [CustomerNo] [int] NOT NULL,
    [AccountNo] [int] NOT NULL,
    [Account] [varchar](500) NOT NULL,
    [ReferredBalance] [FLOAT] NULL,
    [Payment] [FLOAT] NULL
) ON [PRIMARY]

以上是我如何获取数据的示例。

这是一些填充表格的示例数据

INSERT 
INTO  tblProRataSplit(CustomerNo, AccountNo, Account, ReferredBalance, Payment)
VALUES     (75053894,3400266,'PRI',19449.56,150.00)
INSERT 
INTO  tblProRataSplit(CustomerNo, AccountNo, Account, ReferredBalance, Payment)
VALUES     (75053894,3443903,'MRGE',928.42,0.00)
INSERT 
INTO  tblProRataSplit(CustomerNo, AccountNo, Account, ReferredBalance, Payment)
VALUES     (75053894,3494492,'MRGE',14286.55,0.00)
INSERT 
INTO  tblProRataSplit(CustomerNo, AccountNo, Account, ReferredBalance, Payment)
VALUES     (75053894,3494714,'MRGE',912.81,0.00)
INSERT 
INTO  tblProRataSplit(CustomerNo, AccountNo, Account, ReferredBalance, Payment)
VALUES     (75053894,3495910,'MRGE',13613.23,0.00)

如您所见，150.00 的付款已记入 PRI 帐户。我需要拆分 150 英镑的付款，以便将其按余额(不是平均分配)的比例分配给 5 个帐户。

我在这里有点不知所措。如果我不清楚，请告诉我，我会提供更多数据。

Answer 1

; with cte as
(
    select  p.CustomerNo, p.AccountNo, p.Account, p.Payment, 
        m.ReferredBalance,
        pymt = round(m.ReferredBalance * p.Payment / sum(m.ReferredBalance) over (partition by p.CustomerNo), 2),
        rn  = row_number() over (partition by p.CustomerNo order by m.ReferredBalance desc)
    from    tblProRataSplit p
        inner join tblProRataSplit m    on  p.CustomerNo    = m.CustomerNo
    where   p.Account   = 'PRI'
    -- and  m.Account   = 'MRGE'
)
select  *,
    mrge_pymt   = c.pymt
            + case  when rn = 1 then c.Payment - sum(pymt) over (partition by CustomerNo)
                else 0
                end
from    cte c

如果您确实需要更新表，您将需要另一个级别的 CTE