I tried to put together an approach that would work on 2008 and is more set-based. Here is what I came up with.
It did end up being more complex than I had hoped, but it might be an interesting approach for you to benchmark against your current approach on larger data sets. For what it's worth, this script runs in about 15ms for the provided data set on my machine (vs. 75ms for the original script).
As others have mentioned, there are probably other better approaches if you were able to use window functions in 2012+ or maybe a natively compiled procedure in 2014. But it can be fun to think about how to do things without the newer features sometimes!
http://sqlfiddle.com/#!3/ad2be/7
-- Assign each point a sequential rank within each agent's history
SELECT p.internalID, ROW_NUMBER() OVER (PARTITION BY internalID ORDER BY date) AS recordRank, date
INTO #orderedPoints
FROM points p
-- Sort the data for efficient lookup of potential incentives
ALTER TABLE #orderedPoints
ADD UNIQUE CLUSTERED (internalId, recordRank)
-- Identify a potential incentive for any point that has 9+ points in the following 30 days
SELECT s.internalId, s.recordRank, ROW_NUMBER() OVER (PARTITION BY s.internalId ORDER BY s.recordRank) AS potentialIncentiveRank
INTO #potentialIncentives
FROM #orderedPoints s
JOIN #orderedPoints e
ON e.internalId = s.internalId
AND e.recordRank = s.recordRank + 9
AND e.date < DATEADD(dd, 30, s.date)
-- Sort the data to allow for efficient retrieval of subsequent incentives
ALTER TABLE #potentialIncentives
ADD UNIQUE CLUSTERED (internalId, recordRank)
-- A table to hold the incentives achieved
CREATE TABLE #incentives (internalId INT NOT NULL, recordRank INT NOT NULL)
-- A couple transient tables to hold the current "fringe" of incentives that were just inserted
CREATE TABLE #newlyProcessedIncentives (internalId INT NOT NULL, recordRank INT NOT NULL)
CREATE TABLE #newlyProcessedIncentives_forFromClause (internalId INT NOT NULL, recordRank INT NOT NULL)
-- Identify the first incentive for each agent
-- Note that TOP clauses and aggregate functions are not allowed in the recursive portion of a CTE
-- If that were allowed, this could serve as the anchor of a recursive CTE and the loop below would be the recursive portion
INSERT INTO #incentives (internalId, recordRank)
OUTPUT inserted.internalId, inserted.recordRank INTO #newlyProcessedIncentives (internalId, recordRank)
SELECT internalId, recordRank
FROM #potentialIncentives
WHERE potentialIncentiveRank = 1
-- Identify the next incentive for each agent, stopping when no further incentives are identified
WHILE EXISTS (SELECT TOP 1 * FROM #newlyProcessedIncentives)
BEGIN
-- Transfer the most recently identified incentives, so that we can truncate the table to capture the next iteration of incentives
TRUNCATE TABLE #newlyProcessedIncentives_forFromClause
INSERT INTO #newlyProcessedIncentives_forFromClause (internalId, recordRank) SELECT internalId, recordRank FROM #newlyProcessedIncentives
TRUNCATE TABLE #newlyProcessedIncentives
-- Identify the next incentive for each agent
INSERT INTO #incentives (internalId, recordRank)
OUTPUT inserted.internalId, inserted.recordRank INTO #newlyProcessedIncentives (internalId, recordRank)
SELECT nextIncentive.internalId, nextIncentive.recordRank
FROM #newlyProcessedIncentives_forFromClause f
CROSS APPLY (
SELECT TOP 1 p.*
FROM #potentialIncentives p
WHERE p.internalId = f.internalId
AND p.recordRank >= f.recordRank + 10 -- A new incentive can only start after all 10 points from the previous incentive
ORDER BY p.recordRank
) nextIncentive
END
-- Present the final results in the same format as the original implementation
SELECT a.internalId, a.points, i.incentives
FROM ( -- Tabulate points
SELECT internalId, MAX(recordRank) AS points
FROM #orderedPoints
GROUP BY internalId
) a
JOIN ( -- Tabulate incentives achieved
SELECT internalId, COUNT(*) AS incentives
FROM #incentives
GROUP BY internalId
) i
ON i.internalId = a.internalId