Let me preface this answer by stating that I don't know how well this solution would scale, but it does work.
You can retrieve the results you're after by selecting the StateId = 4 rows only and using CROSS APPLY and a subquery to select the StateId = 3 or 7 rows that are after the StateId = 4 rows.
MIN(s1.LogTime) AS DateTime, -- ...
WITH cte AS (
MIN(CASE WHEN stateid IN (3,7)
END) OVER ( PARTITION BY name
ORDER BY datetime ASC
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as datetimestop
Kathi Kellenberger did a comparison of FIFO costing for Redgate in 2010. I used the winning entry method to calculate monthly inventory quantities and values for our Mexico operations. You can search for Set based speed PHreakery if this link does not work. https://www.red-gate.com/simple-talk/sql/performance/set-based-speed-phreakery-the-fifo-stock-...
After looking at custom aggregate functions, I realized that was the appropriate way to solve the issue. In case anyone else comes across this question and would like to see how I solved it:
CREATE OR REPLACE FUNCTION sfunc_outstanding_amount_with_carry
previous_row NUMERIC , -- previous row output
current_row NUMERIC -- current ...
This is what I intended to say in my comment:
select ctr_id, mth, cumulative_pmt_amt
, coalesce(cumulative_amt, 0) as cumulative_amt
, coalesce(cumulative_amt, 0) - cumulative_pmt_amt as balance
select ctr_id, mth
, sum(pmt_amt) over (partition by ctr_id
order by mth) as cumulative_pmt_amt