As Chris said in the comments, the quirky update should be a lot faster. I've been bitten by the rCTEs ' performance when using a lot of information. You can read about the quirky update (QU) in the following article: http://www.sqlservercentral.com/articles/T-SQL/68467/
This could do the trick, but remember to test and follow the QU rules.
CREATE TABLE #test
(
call_state tinyint,
call_type tinyint,
caller int,
localtime datetime,
s_id tinyint,
callee int,
duration int,
[group] int,
filter int
);
CREATE CLUSTERED INDEX IX_TempTest ON #Test(caller, callee, localtime)
INSERT INTO #test(
call_state,
call_type ,
caller ,
localtime ,
s_id ,
callee ,
duration
)
SELECT call_state,
call_type ,
caller ,
localtime ,
s_id ,
callee ,
duration
FROM test
DECLARE @Caller int,
@Callee int,
@Time datetime,
@Group int = 0
UPDATE t SET
@Group = [Group] = @Group + CASE WHEN Caller = @Caller AND callee = @Callee AND ABS( DATEDIFF( ss, localtime, @Time)) <= 1 THEN 0 ELSE 1 END,
@Caller = caller,
@Callee = callee,
@Time = localtime,
filter = CASE WHEN s_id = 2 THEN 1 END
FROM #test t WITH(TABLOCKX)
OPTION(MAXDOP 1)
UPDATE t SET
filter = 1
FROM #test t
WHERE s_id = 6
AND [group] IN (SELECT [group] FROM #test GROUP BY [group] HAVING MAX(filter) IS NULL)
SELECT *,
--If you don't want to update the table twice, you can calculate the filter on the fly.
CASE ROW_NUMBER() OVER( PARTITION BY [group] ORDER BY CASE s_id WHEN 2 THEN 1 WHEN 6 THEN 2 ELSE 3 END) WHEN 1 THEN 1 END filter2
FROM #test
DROP TABLE #test