Fastest way to find the last date in which the running total became negative

What I have so far can calculate the most recent date that the running total was negative, however I need the date of when it became negative. Is this possible without splitting it into multiple temp table / CTE queries to maintain performance?

declare @CustomerCode int=2134

select CustomerCode, CustomerType, max(PaymentDate) as MaxPaymentDate
from (
    select b.CustomerCode, b.CustomerType, b.PaymentDate from Payments as a
    join Payments as b
    on a.CustomerCode = b.CustomerCode and a.CustomerType = b.CustomerType
    where b.PaymentDate <= a.PaymentDate
        AND a.CustomerCode = @CustomerCode 
    group by b.CustomerCode, b.CustomerType, b.PaymentDate
    having sum(b.paymentamount) <= 0
) as T
group by CustomerCode, CustomerType

For example, in the table below I am looking for the row on date 2021-01-07 because the running total of -10 just became negative and it is the most recent time it became negative.

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