0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

DECLARE @Visit TABLE 
    (
            Computer    VARCHAR(20) NOT NULL
        ,   VisitDate   DATETIME NOT NULL
    );
    
    INSERT @Visit (Computer, VisitDate) 
    VALUES
        ('ComputerA', '2012-04-28 09:00:00'),
        ('ComputerA', '2012-04-28 09:05:00'),
        ('ComputerA', '2012-04-28 09:10:00'),
        ('ComputerB', '2012-04-28 09:30:00'),
        ('ComputerB', '2012-04-28 09:32:00'),
        ('ComputerB', '2012-04-28 09:44:00'),
        ('ComputerB', '2012-04-28 09:56:00'),
        ('ComputerB', '2012-04-28 10:25:00'),
        ('ComputerA', '2012-04-28 12:25:00'),
        ('ComputerC', '2012-04-28 12:30:00'),
        ('ComputerC', '2012-04-28 12:35:00'),
        ('ComputerC', '2012-04-28 12:45:00'),
        ('ComputerC', '2012-04-28 12:55:00');
    
    WITH CteBase
    AS
    (
    		SELECT	v.Computer,
    				v.VisitDate,
    				ROW_NUMBER() OVER(PARTITION BY v.Computer ORDER BY v.VisitDate) AS RowNum
    		FROM	@Visit v
    ),	CteRecursive
    AS
    (
    		SELECT	crt.Computer,
    				crt.VisitDate,
    				crt.VisitDate AS GroupStartVisitDate,
    				crt.RowNum,
    				1 AS ComputerVisitRowNum
    		FROM	CteBase crt
    		WHERE	crt.RowNum = 1
    		UNION ALL
    		SELECT	crt.Computer,
    				crt.VisitDate,
    				CASE 
    					WHEN DATEDIFF(MINUTE, prv.GroupStartVisitDate, crt.VisitDate) <= 60 THEN prv.GroupStartVisitDate 
    					ELSE crt.VisitDate 
    				END,
    				crt.RowNum,
    				CASE 
    					WHEN DATEDIFF(MINUTE, prv.GroupStartVisitDate, crt.VisitDate) <= 60 THEN prv.ComputerVisitRowNum + 1
    					ELSE 1
    				END				
    		FROM	CteBase crt
    		INNER JOIN CteRecursive prv ON crt.Computer = prv.Computer 
    		AND		crt.RowNum = prv.RowNum + 1
    )
    SELECT	r.Computer,
    		r.GroupStartVisitDate
    FROM	CteRecursive r
    WHERE	r.ComputerVisitRowNum = 1;

view current version

edited apr 28 12

created apr 28 12 anonymous