The initial intent of your query is to compute the running sum of payments for each customer. I would recommend rephrasing your code to use window functions rather than a self-join. This is neater, and much more efficient:

select p.*,
    sum(amount) over(order by PaymentDate) sum_amount
from payment p
where CustomerCode = @CustomerCode

For a given customer, we can just filter the resultset for negative values, order it by date and retain the top row only:

select top (1) *
from (
    select p.*,
        sum(amount) over(order by PaymentDate) sum_amount
    from payment p
    where CustomerCode = @CustomerCode
) p
where sum_amount < 0
order by PaymentDate

I am not totally sure if you want to use a partition to compute the running sum (your query joins on CustomerCode and CustomerType , but filters on CustomerCode only). If you wanted the same result for multiple customers, we would use:

select top (1) with ties *
from (
    select p.*,
        sum(amount) over(partition by CustomerCode order by PaymentDate) sum_amount
    from payment p
) p
where sum_amount < 0
order by row_number() over(partition by CustomerCode order by PaymentDate)

Update: you want the latest date when each customer's balance became negative (whereas above queries give you the earliest date). We can just tweak the where and order by clauses for this:

select top (1) with ties *
from (
    select p.*,
        sum(amount) over(partition by CustomerCode order by PaymentDate) sum_amount
    from payment p
) p
where sum_amount < 0            -- the balance is negative now
  and sum_amount - amount >= 0  -- and it was positive before
order by row_number() 
    over(partition by CustomerCode order by PaymentDate desc) 
                                -- ^ descending sort to put latest dates first

If I understand correctly, you are looking for the most recent time it changed to a negative value :

So using this query we can clearly say that the most recent time it changed to a negative is 2023-01-07 :

select *
from (
  select p.*,
        sum(PaymentAmount) over(order by PaymentDate) as running_sum
  from Payments p
  where CustomerCode = 123
) as s

Result :

CustomerCode    PaymentDate     PaymentAmount   running_sum
123 retail      2023-01-01      0               0
123 retail      2023-01-02      10              10
123 retail      2023-01-03      -30             -20
123 retail      2023-01-04      10              -10
123 retail      2023-01-05      20              10
123 retail      2023-01-06      10              20
123 retail      2023-01-07      -40             -20     <-- most recent time it changed to a negative value
123 retail      2023-01-08      -10             -30
123 retail      2023-01-09      10              80
123 retail      2023-01-09      100             80

o obtain the expected date, we must group successive rows and then select the first date from the most recent group :

select top (1) CustomerCode, CustomerType, PaymentDate, PaymentAmount
from (
      select *, sum(case when falling = 1 then 0 else 1 end) over (order by PaymentDate) as grp
      from (
             select *, case when running_sum < 0 then 1 else null end as falling
             from (
                 select p.*, sum(PaymentAmount) over(order by PaymentDate) as running_sum,
                 row_number() over(order by PaymentDate) as rn
                 from Payments p
                 where CustomerCode = 123
             ) as c1
      ) as c2
) as c3
where running_sum < 0
order by grp desc, rn

Result :

CustomerCode    CustomerType    PaymentDate PaymentAmount
123             retail          2023-01-07  -40

Demo here