Bit of a guess.

SELECT activity, oldSponser, oldSibling, newSponser, start+rn AS newSibling

FROM (SELECT activity, sponsor, sibling, 4, ROW_NUMBER() OVER(ORDER BY activity)

FROM @activity

WHERE sponsor <> 4

GROUP BY activity, sponsor, sibling) a(activity,oldSponser,oldSibling,newSponser,rn)

CROSS APPLY (SELECT MAX(sibling)

FROM @activity

WHERE sponsor = 4) b(start);

In case I'm wrong and this is a running total problem, check out this awesome article by Jeff Moden --> http://www.sqlservercentral.com/articles/T-SQL/68467/%5B/url%5D