One option that I have not seen mentioned is to use the OFFSET/FETCH that was introduced in SQL 2012 specifically for pagination.  What you want is essentially pagination with one...

Well, my new job has been keeping my very busy, so I haven't had much time to come here, and even less time to post.

Try changing your join to the following

INNER JOIN #test child ON child.Key_txt LIKE Parent.Key_txt + '[0-9]%'
   AND LEN(child.Key_txt) - 16=LEN(Parent.Key_txt)  /* I think you had these backwards.  */

You haven't supplied a value for the ELSE condition in your CASE expression, so it is using the default NULL value.  If you want it to be 0, you should...

According to SUM (Transact-SQL), SUM() OVER(<partition> ORDER BY <order clause> ) is available starting in SQL-2008, which is what I was remembering.

Drew

SELECT @dtStart = ISNULL(CONVERT(INT, CONVERT(CHAR(8), @dtsStart, 112)), @dtEffective)

There's probably nothing you can do about this, but representing dates as integers is a bad idea.

Drew

Reported as spam.