DECLARE @cap int
SET @cap = 100000

DECLARE @table TABLE (ayq int,cyq int, paid int)
INSERT INTO @table  
SELECT 200604,	200604,	2125   UNION ALL
SELECT 200604,	200701,	305   UNION ALL
SELECT 200604,	200702,	0   UNION ALL
SELECT 200604,	200703,	0   UNION ALL
SELECT 200604,	200704,	0   UNION ALL
SELECT 200604,	200802,	0   UNION ALL
SELECT 200604,	200903,	0   UNION ALL
SELECT 200604,	200904,	107000
 

SELECT t2.ayq,t2.cyq,t2.paid
      ,t2.totpaid
      ,[IncrementalPaid] = CASE WHEN @cap - t2.totpaid < 0 THEN @cap - (t2.TotPaid - t2.paid) ELSE t2.paid END
FROM
	(
		select t1.ayq,t1.cyq, t1.paid
			  ,[TotPaid] = sum(t2.paid)
			  --,@cap -  sum(t2.paid)  
		 from @table t1
		inner join
		  @table t2 
		on
			t1.ayq = t2.ayq 
		and t1.cyq >= t2.cyq

		group by t1.ayq,t1.cyq, t1.paid
	) t2

RESULT SET:

ayq	cyq	paid	totpaid	IncrementalPaid
200604	200604	2125	2125	2125
200604	200701	305	2430	305
200604	200702	0	2430	0
200604	200703	0	2430	0
200604	200704	0	2430	0
200604	200802	0	2430	0
200604	200903	0	2430	0
200604	200904	107000	109430	97570

DECLARE @ayq INT
DECLARE @cyq INT 
DECLARE @paid INT
DECLARE @ayqLast INT --SET @ayqLast = 0
DECLARE @totpaid INT

DECLARE @tableOut TABLE (ayq int,cyq int, paid int, 
		totpaid INT) --, [IncrementalPaid] INT)
  

DECLARE cur CURSOR FORWARD_ONLY FOR
SELECT ayq, cyq, paid
FROM @Table
ORDER BY ayq, cyq

OPEN cur
FETCH NEXT FROM cur INTO @ayq, @cyq, @paid
WHILE @@Fetch_Status = 0
BEGIN
	IF @ayqLast = @ayq
	BEGIN
		-- The ayq value is the same as the previous record.
		SET @totpaid = @totpaid + @paid
	END
	ELSE
	BEGIN
		-- The ayq value has changed.
		SET @totpaid = @paid
	END

	INSERT INTO @tableOut
	VALUES (@ayq, @cyq, @paid, @totpaid)

	SET @ayqLast = @ayq

	FETCH NEXT FROM cur INTO @ayq, @cyq, @paid	
END

CLOSE cur
DEALLOCATE cur

SELECT ayq,cyq,paid
      ,totpaid
      ,[IncrementalPaid] = CASE WHEN @cap - totpaid < 0 THEN @cap - (TotPaid - paid) ELSE paid END

FROM @tableOut

quote:

---

Originally posted by waterduck

;with cte as(
select ROW_NUMBER() over (order by ayq, cyq) num, *
from @table
)
select a.ayq, a.cyq, a.paid, incre, a.paid-isnull(b.paid, 0)
from cte a left join cte b on a.ayq=b.ayq and a.num=b.num+1
cross apply
(select SUM(paid)incre from cte c where c.cyq<=a.cyq and c.ayq=a.ayq)c

---

		select  t2.policynumber,t2.claimnumber
		  ,  t2.ayq,t2.cyq
		 ,t2.paid,t2.totpaid,t2.incurred,t2.totincurred
		  ,[IncrementalPaid] = 
								CASE WHEN 
										   CASE WHEN @cap - t2.totpaid < 0 
												THEN @cap - (t2.TotPaid - t2.paid) 
												ELSE t2.paid 
											END > 0
									 THEN CASE WHEN @cap - t2.totpaid < 0 THEN @cap - (t2.TotPaid - t2.paid) ELSE t2.paid END
									 ELSE 0
								 END
		  ,[IncrementalIncurred] =
                           CASE WHEN t2.totincurred < @cap and t2.incurred < 0 then t2.incurred else 
								CASE WHEN -- when you're over the cap
										   CASE WHEN @cap - t2.totincurred < 0 
												THEN @cap - (t2.totincurred - t2.incurred) 
												ELSE t2.incurred
											END > 0
									 THEN 
                                           CASE WHEN @cap - t2.totincurred < 0 
                                                THEN @cap - (t2.totincurred - t2.incurred) 
                                                ELSE t2.incurred 
                                          END 
                                     ELSE --If you went back under the cap due to take down
                                          CASE 
                                             WHEN t2.totincurred < @cap and t2.incurred < 0 
                                             THEN t2.totincurred - @cap 
                                             ELSE 0 
                                          END
                                 END 
                                           
end
 
		from
		(
			 select  t1.policynumber,t1.claimnumber
            ,t1.ayq,t1.cyq, t1.paid ,t1.incurred--,t1.incexp,t1.expenses
				  ,[TotPaid] = sum(t2.paid)
				  ,[TotIncurred]  = sum(t2.incurred)  
			 from #t1  t1
			inner join
			  #t1 t2 
			on
				t1.policynumber = t2.policynumber
			and t1.claimnumber  = t2.claimnumber
			 and	t1.ayq = t2.ayq 
			and t1.cyq >= t2.cyq
       --     where t1.policynumber = 'aka000002' and t1.claimnumber = 214125
			group by   t1.policynumber,t1.claimnumber, t1.ayq,t1.cyq, t1.paid,t1.incurred  
		) t2
 


order by policynumber,claimnumber,cyq
END