MickyT must have a permanent TABLE in his sandbox with the name TALLY. You can substitute that for an in line Tally table (assuming you have access to the sys tables) as follows:

WITH TALLY (N) AS (

SELECT TOP (SELECT MAX(DaySupply) FROM #MemberInfo)

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns a CROSS JOIN sys.all_columns b

),

fillDays AS (

SELECT MemberID, Strength, DATEADD(day, t.N, DateFilled) myDate

FROM #MemberInfo m

CROSS APPLY (SELECT TOP(DaySupply) N - 1 N FROM TALLY) t

),

sumStrengths AS (

SELECT MemberID, myDate, SUM(Strength) SumStrength, COUNT(*) numScripts

FROM fillDays

GROUP BY MemberID, myDate

)

SELECT MemberID, myDate, SumStrength, numScripts,

CASE WHEN SumStrength >= 100 THEN -- only display when over

ROW_NUMBER() OVER (

PARTITION BY MEMBERID, CASE WHEN SumStrength >= 100 THEN 1 ELSE 0 END

ORDER BY myDate)

END consecutiveDaysOver

FROM sumStrengths

ORDER BY myDATE;

You can Google "Tally table" for many links, but basically it is just a table with one column that's a number from 1 (or 0) up to some large N (like 10,000,000). I've included a TOP clause on the in line Tally to limit to just the MAX number of Days supply in your table to keep the performance good.

I also dropped the ORDER BY in MickyT's CROSS APPLY to TALLY as that's not needed.

Here's one link you can look at for info on Tally tables: http://www.sqlservercentral.com/articles/Tally+Table/72993/