UPDATE #Counter2
SET @Counter = Counter = CASE WHEN (@Counter >= 1 AND @Counter < 10) OR ([Value] >= 10 AND [Value] <=15 AND Code = 'a')
                           THEN @Counter + 1
                           ELSE 0 -- restart the counter
                           END
   ,@RowNumber = RowNumber
FROM #Counter2

--===== Create a temporary working table with a dummy unique identifier (RowNumber)
     -- that will also be the object of the Clustered Index as a PK.  Notice also,
     -- that we've added a "Counter" column to hold the result of your request.
 CREATE TABLE #Counter2
(
        RowNumber INT IDENTITY(1,1),
        StartDate DATETIME,
        Value FLOAT,
        Code CHAR(1),
        Counter INT
)

--===== Copy all existing data from original table into our results table
     -- in the correct order.
 INSERT INTO #Counter2(StartDate, Value, Code)
 SELECT StartDate, Value, Code 
   FROM #Counter
  ORDER BY StartDate 

--===== MUST have a clustered primary key to GUARANTEE this will work
  ALTER TABLE #Counter2
    ADD PRIMARY KEY CLUSTERED (RowNumber)

--===== Create the required local variables
DECLARE @Counter INT, -- Start value is NULL
        @RowNumber    -- Dummy variable to "anchor" the "running" update

--===== Calculate and store the value of the counter in the table using a "pseudo-cursor"
     -- the order of which is driven by a forced scan on the Clustered Index.
     -- Rules for the counter value are...
     --
     -- 1. Any value not in the following criteria will result in NULL for the Counter
     -- 2. Data must be processed in order by StartDate.
     -- 3. Counter will start at "1" at the first occurance where the Value is between 10 and 15 and 
     --    and the Code is "a".
     -- 4. Count will increase by 1 
     -- 5. When the Counter reaches the value of 10, reset the Counter to NULL
     -- 6. Do until all rows in RowNumber order have been updated.
 UPDATE #Counter2
    SET @Counter = Counter = CASE WHEN ([Value] BETWEEN 10 AND 15) AND Code = 'a' AND @Counter IS NULL THEN 1 -- Rules 1 & 3
                                  WHEN @Counter = 10 THEN NULL -- Rule 5
                                  ELSE @Counter + 1 -- Rule 4
                              END,
        @RowNumber = RowNumber
   FROM #Counter2 WITH(INDEX(0)) --LOOK!  Must refer to the clustered index or could bomb! -- Rules 2 and 6

--===== Display the results in RowNumber order
 SELECT * FROM #Counter2 ORDER BY RowNumber

--===== Housekeeping so we can run this example again, if we want.
     -- This is NOT necessary in final production stored procs because
     -- it will automatically drop at the end of the proc (how poetic 
   DROP TABLE #Counter2