Date sum with condition?

Question

I have a base with the 3 columns and one that I created.

╔═══════════════════════════╦═════════════════════╦════════════════════════╦═══════════╗
║             DATA          ║       LATITU        ║         LONGIT         ║  ROW      ║
╠═══════════════════════════╬═════════════════════╬════════════════════════╬═══════════╣
║    2021-02-10 10:32:00    ║  -27,0960066666667  ║     -52,6380843333333  ║    1      ║
║    2021-02-10 09:28:00    ║  -27,0960966666667  ║     -52,6380933333333  ║    2      ║
║    2021-02-10 08:27:00    ║  -27,0960966666667  ║     -52,6380933333333  ║    1      ║
║    2021-02-10 07:26:00    ║  -27,0961566666667  ║     -52,6380950000000  ║    1      ║
║    2021-02-10 06:24:00    ║  -27,0961466666667  ║     -52,6381016666667  ║    2      ║
║    2021-02-10 05:24:00    ║  -27,0961466666667  ║     -52,6381016666667  ║    1      ║
║    2021-02-10 03:23:00    ║  -27,0963583333333  ║     -52,6381350000000  ║    1      ║
╚═══════════════════════════╩═════════════════════╩════════════════════════╩═══════════╝

My goal is to create a column that sums the DATA column in format (hh:mm) while in motion (the columns LATITU and LONGIT are different values), reset when stop and restart the sum when moving again. (Remembering that I summarized the data here, because in the real base the date comes every 5 minutes and the ROW column sometimes arrives until in a sequence that 100).

Example:

---

TIME:

01:04

00:00

02:03

01:02

00:00

02:01

00:00 - INICIO

To help created this column called "ROW", in it whenever the Latitude and Longitude is different (this in motion) it results in "1" and when the LAT/LONG is equal (is stopped) starts a sequence (1,2,3,4) until changing the lat/long (return to the movement) where it results again in 1.

SQL:

SELECT DATA, LATITU, LONGIT, ROW_NUMBER() 
OVER (PARTITION BY LATITU,LONGIT ORDER BY DATA ASC) as ROW  
FROM TABELA_LATLONG
ORDER BY 
DATA DESC

Answer 1

Here is a suggested test using a recursive CTE:

WITH
  CTE_RN AS
  (
    SELECT
      DATA,
      LATITU,
      LONGIT,
      ROW_NUMBER() OVER(ORDER BY DATA) AS RN
    FROM TABELA_LATLONG
  ),
  
  CTE_REC AS
  (
    SELECT
      DATA,
      LATITU,
      LONGIT,
      RN,
      DATA AS DATA_INI,
      CAST('00:00' AS DATETIME) AS TEMPO
    FROM CTE_RN
    WHERE
      RN = 1
      
    UNION ALL
    
    SELECT
      N.DATA,
      N.LATITU,
      N.LONGIT,
      N.RN,
      CASE WHEN C.LATITU = N.LATITU AND C.LONGIT = N.LONGIT
        THEN N.DATA
        ELSE C.DATA_INI
      END,
      CASE WHEN C.LATITU = N.LATITU AND C.LONGIT = N.LONGIT
        THEN CAST('00:00' AS DATETIME)
        ELSE 
          DATEADD
            (MINUTE,
             DATEDIFF
               (MINUTE,
                C.DATA_INI,
                N.DATA),
             0)
      END     
    FROM CTE_REC AS C
    INNER JOIN CTE_RN AS N
      ON N.RN = C.RN + 1
  )

SELECT
  DATA,
  LATITU,
  LONGIT,
  TEMPO
FROM CTE_REC
ORDER BY 
  DATA DESC

And another suggestion using a subquery:

WITH CTE_RN AS
(
  SELECT 
    DATA, 
    LATITU, 
    LONGIT, 
    ROW_NUMBER() OVER(PARTITION BY LATITU,LONGIT ORDER BY DATA ASC) as ROW_PART,
    ROW_NUMBER() OVER(ORDER BY DATA ASC) as ROW_GERAL
  FROM TABELA_LATLONG
)

SELECT
  T.DATA,
  T.LATITU,
  T.LONGIT,
  CASE WHEN ROW_GERAL = 1 OR ROW_PART > 1 
    THEN CAST('00:00' AS DATETIME)
    ELSE
      DATEADD
        (MINUTE,
         DATEDIFF
           (MINUTE, 
            (SELECT TOP(1) I.DATA FROM CTE_RN AS I
             WHERE
               I.DATA < T.DATA AND
               (I.ROW_GERAL = 1 OR I.ROW_PART > 1)
             ORDER BY
               I.DATA DESC),
            T.DATA),
         0)
  END AS TEMPO
FROM CTE_RN AS T
ORDER BY 
  T.DATA DESC

Edit: Below is the version with subquery modified to format the Time column in the format hh:mm

WITH CTE_RN AS
(
  SELECT 
    DATA, 
    LATITU, 
    LONGIT, 
    ROW_NUMBER() OVER(PARTITION BY LATITU,LONGIT ORDER BY DATA ASC) as ROW_PART,
    ROW_NUMBER() OVER(ORDER BY DATA ASC) as ROW_GERAL
  FROM TABELA_LATLONG
)

SELECT
  T.DATA,
  T.LATITU,
  T.LONGIT,
  CASE WHEN ROW_GERAL = 1 OR ROW_PART > 1 
    THEN '00:00'
    ELSE
      LEFT
        (CONVERT
           (VARCHAR(10),
            DATEADD
              (MINUTE,
               DATEDIFF
                 (MINUTE, 
                  (SELECT TOP(1) I.DATA FROM CTE_RN AS I
                   WHERE
                     I.DATA < T.DATA AND
                     (I.ROW_GERAL = 1 OR I.ROW_PART > 1)
                   ORDER BY
                     I.DATA DESC),
                  T.DATA),
               0),
            108),
         5)   
  END AS TEMPO
FROM CTE_RN AS T
ORDER BY 
  T.DATA DESC

I hope it helps