Hi

I think this is what you are asking for. I've made use of a Tally(Numbers) table to fill out a strength for each day, then summed strength for each member and day. The consecutive days are done using a row_number. I used CTEs to try and make the query a bit clearer and put in script counter for my testing

WITH fillDays AS (

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

FROM #MemberInfo m

CROSS APPLY (SELECT TOP(DaySupply) N - 1 N FROM TALLY ORDER BY N) 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;