Calculating distance using geometry of x and y location in SQL

I’m using SQL Server and I need to calculate the distance between the x and y of a frame and the previous x and y of a frame where the day, team, and member are all the same. Currently, I have this code that works but doesn’t accomplish what I need. I’m getting every distance permutation of the x and y location where the day, team, and member are all the same.

I need help to incorporate frames into the query so that I get the N+1 Frame x and y location minus the N Frame x and y location.

CREATE TABLE TestTable (
  Day int NULL,
  Frame int NULL, 
  Team int NULL,
  Member int NULL,
  x float NULL,
  y float NULL
);

Insert into a Values 
  (1, 1, 1, 1, 1486.64, 2017.55),
  (1, 1, 1, 2, 1754.55, 1495.81),
  (1, 1, 2,1, 2049.15, 876.349),
  (1, 2, 1, 1, 1707.59, 1171.22),
  (1, 2, 1, 2, 1432.56, 1459.99),
  (1, 2, 2, 1, 1470.27, 1086.22),
  (1, 3, 1, 1, 3639.19, 1281.36),
  (1, 3, 1, 2, 2751.37, 976.348),
  (1, 3, 2, 1, 2496.69, 1283.29),
  (1, 4, 1, 1, 2347.26, 984.255),
  (1, 4, 1, 2, 2044.92, 711.154),
  (1, 4, 2, 1, 2473.65, 1816.23);


Select A.Day, A.Frame, A.Team, A.Member, 
GEOMETRY::Point(A.[x], A.[y], 0).STDistance(GEOMETRY::Point(B.[x], B.[y], 0)) As Distance
From a A
Join a B
ON A.Day = B.Day and A.Team = B.Team and A.Member = B.Member

I also may deal with NULL x and y values so if it’s possible to add this to the query too.

Where A.x IS NOT NULL and A.y IS NOT NULL

Ultimately I want to track the distance of every member throughout the day, frame by frame.Later, I’ll add up each member’s total distance for the day.

Answer

;WITH CTE1 AS
(
    SELECT 
        [day], team, member, frame, x, y, 
        LAG(x) OVER (PARTITION BY [day], team, member ORDER BY frame) AS PervFrameX,
        LAG(y) OVER (PARTITION BY [day], team, member ORDER BY frame) AS PervFrameY
    FROM 
        TestTable
    WHERE
        X IS NOT NULL AND Y IS NOT NULL
),
CTE2 AS
(
    SELECT 
        [day], team, member, frame, x, y, PervFrameX, PervFrameY, 
        IIF(PervFrameX IS NULL OR PervFrameY IS NULL, 0,
            GEOMETRY::Point(x, y, 0).STDistance(GEOMETRY::Point(PervFrameX, PervFrameY, 0))) As Distance 
    FROM 
        CTE1
)
SELECT 
    *, 
    SUM(Distance) OVER (PARTITION BY [day], team, member) AS MemberTotalDistance,
    SUM(Distance) OVER (PARTITION BY [day]) AS DailyTotalDistance
FROM 
    CTE2
ORDER BY 
    [day], team, member, frame

CTE1 and CTE2 are used to improve readability of the query.

Output:

    day team member frame x         y       PervFrameX PervFrameY Distance  MemberTotalDistance DailyTotalDistance
    1   1     1     1     1486.64   2017.55 NULL        NULL       0.000    4135.086           8812.698
    1   1     1     2     1707.59   1171.22 1486.64     2017.55    874.696  4135.086           8812.698
    1   1     1     3     3639.19   1281.36 1707.59     1171.22    1934.738 4135.086           8812.698
    1   1     1     4     2347.26   984.255 3639.19     1281.36    1325.652 4135.086           8812.698
    1   1     2     1     1754.55   1495.81 NULL        NULL       0.000    2483.257           8812.698
    1   1     2     2     1432.56   1459.99 1754.55     1495.81    323.976  2483.257           8812.698
    1   1     2     3     2751.37   976.348 1432.56     1459.99    1404.695 2483.257           8812.698
    1   1     2     4     2044.92   711.154 2751.37     976.348    754.586  2483.257           8812.698
    1   2     1     1     2049.15   876.349 NULL        NULL       0.000    2194.355           8812.698
    1   2     1     2     1470.27   1086.22 2049.15     876.349    615.750  2194.355           8812.698
    1   2     1     3     2496.69   1283.29 1470.27     1086.22    1045.167 2194.355           8812.698
    1   2     1     4     2473.65   1816.23 2496.69     1283.29    533.438  2194.355           8812.698