SQL - Calculate the difference between rows using another column to indicate changes

Question

I have a table like this and I want to calculate the difference between FrameNum when TeamId is the same. I need to treat the TeamId column as a switch. As soon as TeamId changes, I need to calculate the difference between the StartFrameNum and EndFrameNum. There's a case where you may only have a single TeamId(row 12), so I think I'll need to grab the next FrameNum then subtract the starting FrameNum. I'm open to different ways of doing this if it makes sense.

FrameNum	TeamId
8398	1
8420	1
8452	1
8478	2
8494	2
8511	1
8545	1
8563	2
8604	2
8641	2
8698	2
8708	1
8741	2
8772	2

My desired output looks like this.

StartFrameNum	EndFrameNum	Duration	TeamId
8398	8478	80	1
8478	8511	33	2
8511	8563	52	1
8563	8708	145	2
8708	8741	33	1
8741	8772	33	2

This is the query I've started writing. It's not correct. I don't know if I'm going in the right direction. Any help or advice would be appreciated. I'm using SQL Server. Thanks in advance.

WITH CTE AS 
(
    SELECT 
        TeamId, FrameNum, 
        ROW_NUMBER() OVER (PARTITION BY RN1 - RN2 ORDER BY FrameNum) RN3, 
        RN1
    FROM 
        (SELECT 
             TeamId, FrameNum,
             ROW_NUMBER() OVER (ORDER BY FrameNum) RN1,
             ROW_NUMBER() OVER (PARTITION BY TeamId ORDER BY FrameNum) RN2
         FROM 
             Table) T
)
SELECT 
    C1.TeamId, C1.FrameNum,
    C1.FrameNum - X.FrameNum [IndividualDifference]
FROM
    CTE C1
OUTER APPLY 
    (SELECT MAX(FrameNum)
     FROM CTE
     WHERE RN3 = 1
       AND RN1 < C1.RN1) X(FrameNum)
WHERE 
    C1.RN3 = 1  
ORDER BY 
    C1.FrameNum;

Answer 1

This is a type of gaps-and-islands problem, for which there are a number of solutions.

Here is one:

• We start by using LAG to define the island starting point
• We use COUNT OVER to create a group ID
• We group by that and pull out the aggregated info we need from each group

WITH StartPoints AS (
    SELECT *,
      CASE WHEN LAG(TeamId) OVER (ORDER BY FrameNum) = TeamId THEN NULL ELSE 1 END IsStart
    FROM YourTable t
),
Groupings AS (
    SELECT *,
      COUNT(IsStart) OVER (ORDER BY FrameNum ROWS UNBOUNDED PRECEDING) GroupId
    FROM StartPoints
)
SELECT
  MIN(FrameNum) AS StartFrameNum,
  LEAD(MIN(FrameNum), 1, MAX(FrameNum)) OVER (ORDER BY GroupId) EndFrameNum,
  LEAD(MIN(FrameNum), 1, MAX(FrameNum)) OVER (ORDER BY GroupId) - MIN(FrameNum) Duration,
  MIN(TeamId) TeamId
FROM Groupings
GROUP BY GroupId

db<>fiddle

Answer 2

You don't need aggregation for this, just window functions:

select teamid, framenum as start_framenum,
       lead(framenum, 1, last_framenum) over (order by framenum) as end_framenum,
       (lead(framenum, 1, last_framenum) over (order by framenum) - framenum) as duration
from (select t.*,
             lag(teamid) over (order by framenum) as prev_teamid,
             max(framenum) over () as last_framenum
      from t
     ) t
where prev_teamid is null or pre_teamid <> teamid;

The idea is to keep the first row for each team when the team changes. Then after filtering use lead() to get the next framenum, because you want the result to perfectly tile.

You need the maximum framenum just for the last row.