Запрос для отображения всех покупок клиентов

Вы можете сделать это, используя оконные функции lag() для получения предыдущей строки и sum() для получения итоговой суммы:

AllPurchases используется для объединения данных из обеих таблиц и расчета промежуточной суммы.

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

cte3 отвечает за определение оставшегося кредита на основе определенных условий, включая сравнение текущего «total» и предыдущего «running_total», полученного в cte2.

WITH AllPurchases AS (
    SELECT
        t2.Id_Client,
        t2.DocDate,
        t2.Amount,
        t2.Caption,
        t1.Value as total,
        sum(Amount) over (partition by t2.Id_Client order by DocDate desc ROWS UNBOUNDED PRECEDING) as running_total
    FROM
        Table2 t2
    INNER JOIN Table1 t1 ON t1.Id_Client = t2.Id_Client
),
cte2 as (
  select *, lag(running_total) over (partition by Id_Client order by DocDate desc) as lag_running_total
  from AllPurchases
),
cte3 as (
  select *, case when total>= running_total then Amount
               when lag_running_total is null then total
               else total - lag_running_total
               end as remaining_credit
  from cte2
)
select Id_Client, DocDate, remaining_credit, Caption
from cte3
where remaining_credit > 0

Полученные результаты :

Id_Client   DocDate remaining_credit    Caption
1   2005-10-24 00:00:00.000 5.0000  qh
1   2005-10-22 00:00:00.000 3.0000  ek
1   2005-10-19 00:00:00.000 9.0000  wj
1   2005-10-18 00:00:00.000 6.0000  tz
1   2005-10-04 00:00:00.000 1.0000  rl
2   2005-10-23 00:00:00.000 11.0000 uc
2   2005-10-21 00:00:00.000 2.0000  iv
3   2005-10-30 00:00:00.000 2.0000  pn
4   2005-10-23 00:00:00.000 5.0000  dw

Демо здесь

Вот альтернативная версия:

SELECT  Id_Client
,   DocDate
,   CASE WHEN aggSum <= Value THEN Amount ELSE Amount + Value - aggSum END AS AmountTop
,   Caption
FROM    (
    SELECT  *
    ,   ROW_NUMBER() OVER(PARTITION BY ID_Client, CASE WHEN aggSum >= Value THEN 0 ELSE 1 END ORDER BY DocDate DESC, Caption DESC) AS sort
    FROM    (
        SELECT  SUM(Amount) OVER(PARTITION BY t2.ID_Client ORDER BY DocDate DESC, Caption DESC/* ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW*/) AS aggSum, t2.*, t.Value
        FROM    @Table2 t2
        INNER JOIN @Table1 t
            ON  t.Id_Client = t2.Id_Client
        ) x
    ) x
WHERE   aggSum <= Value OR sort = 1
ORDER BY Id_Client, DocDate DESC, Caption DESC

Он вычисляет промежуточную сумму и сравнивает ее с доступным кредитом.

Разделы ROW_NUMBER() разбиты на две разные группы: те, где кредит меньше совокупной потраченной суммы, и те, где она больше.

CASE WHEN aggSum >= Value THEN 0 ELSE 1 END поэтому, если клиент превышает свой кредит в агрегированной сумме, он создает группу 0, в противном случае - группу 1, тогда ORDER BY DocDate DESC создает последовательность значений 1, 2, 3. Это означает, что первая строка, в которой клиент превышает кредит, получает значение номера строки = 1, вторая строка получит = 2 и т. д.

И поскольку нас интересует первая строка, в которой кредит клиента перерасходован, мы включаем эту строку в наш расчет в часть WHERE aggSum <= Value OR sort = 1.

Он обрабатывает строки с одинаковой датой, используя заголовок в качестве разрешения конфликтов.