Как я могу распределить значение по нескольким строкам без курсора?

кажется я нашел решение

Во-первых, мне не нужно связывать платные транзакции с транзакциями по счетам, поэтому мне нужна только сумма всех платежей.

select accountid, sum(paid)
from mytable
where type = 'Payment'
group by accountid

Затем мне нужно применить это значение к каждой записи, пока промежуточная сумма не станет больше, чем общая сумма выплат.

Для этого я изменил свой запрос промежуточной суммы, чтобы он суммировал только начисления, а не начисления и платежи.

;with cte(id, accountid, invoiced, paid, current)
as (
    select id, accountid, invoiced, paid, current
        , row_number() over (order by datecreated)
    from mytable
    where type = 'Charge'
)

select t1.id, t1.accountid, t1.invoiced, sum(t2.invoiced) as [runningTotalOfCharges]
from cte as t1
join cte as t2 on t1.number = t2.number and t2.rownum <= t1.rownum
group by t1.id, t1.accountid, t1.invoiced

и присоединил это к платежному запросу, так что теперь у меня есть набор строк, содержащих общую сумму платежа, промежуточную сумму сборов до этой записи и сумму сборов текущей записи.

Оттуда мне просто понадобился оператор CASE, чтобы определить, была ли оплата полностью оплачена, частично оплачена или не оплачена вообще, и использовать немного математики, чтобы выяснить записи Paid и Current.

select charged.Id, charged.AccountId, charged.Invoiced
    -- Use Case statements to determine if this payment is fully paid, partially paid, 
    -- or not paid at all, then determine Current and Paid based on that
    , case when totalpaid - runningtotal >= 0 then invoiced 
        when invoiced > abs(totalpaid - runningtotal) then invoiced + totalpaid - runningtotal
        else 0 end as [Paid]
    , case when totalpaid - runningtotal >= 0 then 0 
        when invoiced > abs(totalpaid - runningtotal) then abs(totalpaid - runningtotal)
        else invoiced end as [Current]
from 
(
    -- Running total query from above
    select t1.id, t1.accountid, t1.invoiced, sum(t2.invoiced) as [runningtotal]
    from cte as t1
    join cte as t2 on t1.number = t2.number and t2.rownum <= t1.rownum
    group by t1.id, t1.accountid, t1.invoiced
) as charged

inner join (
    -- Total Paid query from above
    select accountid, sum(paid) as totalpaid
    from mytable
    where type = 'Payment'
    group by accountid
) as paid on charged.number = paid.number

А конечный результат такой, какой я хочу. Просто нужно присоединить это к фактической таблице данных через столбец Id и обновить значения Paid и Current :)

Id    AccountId    Invoiced    Paid    Current
1     1            100         100     0
2     1            100         100     0
3     1            100         75      25

Вы можете рассчитать промежуточные итоги и суммы для платных и текущих столбцов, а затем выполнить некоторые математические операции, чтобы получить текущие значения столбцов. Я попробовал, но увяз в математике, чтобы получить окончательные значения. Это не простой путь, но это путь.

DECLARE @myTable TABLE
(
    [TranId] INT,
    [Type] VARCHAR(10),
    [Invoiced] INT,
    [Paid] INT,
    [Current] INT,
    [SumPaid] INT,
    [SumCurrent] INT,
    [RunningPaid] INT,
    [RunningCurrent] INT
)

INSERT INTO @myTable SELECT 1, 'Charge', 100, 0, 100, null, null, null, null
INSERT INTO @myTable SELECT 2, 'Charge', 100, 0, 100, null, null, null, null
INSERT INTO @myTable SELECT 3, 'Charge', 100, 0, 100, null, null, null, null
INSERT INTO @myTable SELECT 4, 'Paid', 0, 250, 0, null, null, null, null
INSERT INTO @myTable SELECT 5, 'Paid', 0, 25, 0, null, null, null, null


UPDATE @myTable SET SumPaid = (SELECT SUM([Paid]) FROM @myTable)
UPDATE @myTable SET SumCurrent = (SELECT SUM([Current]) FROM @myTable)
UPDATE @myTable
SET 
    [RunningPaid] = full_running_total_set.[RunningPaid], 
    [RunningCurrent] = full_running_total_set.[RunningCurrent]
FROM @myTable
INNER JOIN
(
    SELECT
        TranId1,
        SUM([Paid]) AS [RunningPaid],
        SUM([Current]) AS [RunningCurrent]
    FROM
    (
        SELECT
            set2.[Paid],
            set2.[Current],
            set1.[TranId] AS [TranId1],
            set2.[TranId] AS [TranId2]
        FROM @myTable set1
        INNER JOIN @myTable set2
            ON set1.[TranId] >= set2.[TranId]
    )running_total_set
    GROUP BY [TranId1]
)full_running_total_set
ON [TranId] = full_running_total_set.[TranId1]

SELECT * FROM @myTable