MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (
  `LogDateAndTime` VARCHAR(20),
  `BatchDate` VARCHAR(20),
  `TagLetter` VARCHAR(1),
  `Totaliser` INTEGER
);

INSERT INTO tablename
  (`LogDateAndTime`, `BatchDate`, `TagLetter`, `Totaliser`)
VALUES
  ('10-11-2020 09:06:14', '10-11-2020 08:29:55', 'A', '6319'),
  ('10-11-2020 09:06:24', '10-11-2020 08:29:55', 'A', '6337'),
  ('10-11-2020 09:08:14', '10-11-2020 08:29:55', 'B', '6355'),
  ('10-11-2020 09:08:24', '10-11-2020 08:29:55', 'B', '6372'),
  ('10-11-2020 09:08:34', '10-11-2020 08:29:55', 'B', '6378'),
  ('10-11-2020 09:08:44', '10-11-2020 08:29:55', 'A', '6383'),
  ('10-11-2020 09:09:14', '10-11-2020 08:29:55', 'A', '6388'),
  ('10-11-2020 09:09:24', '10-11-2020 08:29:55', 'A', '6396'),
  ('10-11-2020 09:09:34', '10-11-2020 08:29:55', 'B', '6409'),
  ('10-11-2020 09:09:44', '10-11-2020 08:29:55', 'B', '6426'),
  ('10-11-2020 09:10:24', '10-11-2020 08:29:55', 'B', '6442');
WITH cte AS (
  SELECT DISTINCT SUM(MAX(Totaliser) - MIN(Totaliser)) OVER () ExpectedResult
  FROM (
    SELECT *, SUM(flag) OVER (ORDER BY LogDateAndTime) grp
    FROM (
      SELECT *, LAG(TagLetter, 1, '') OVER (ORDER BY LogDateAndTime) <> 'A' flag
      FROM tablename 
    ) t
    WHERE TagLetter = 'A'
  ) t
  GROUP BY grp
)
SELECT t.*, c.ExpectedResult
FROM tablename t CROSS JOIN cte c


LogDateAndTime
BatchDate
TagLetter
Totaliser
ExpectedResult


10-11-2020 09:06:14
10-11-2020 08:29:55
A
6319
31

10-11-2020 09:06:24
10-11-2020 08:29:55
A
6337
31

10-11-2020 09:08:14
10-11-2020 08:29:55
B
6355
31

10-11-2020 09:08:24
10-11-2020 08:29:55
B
6372
31

10-11-2020 09:08:34
10-11-2020 08:29:55
B
6378
31

10-11-2020 09:08:44
10-11-2020 08:29:55
A
6383
31

10-11-2020 09:09:14
10-11-2020 08:29:55
A
6388
31

10-11-2020 09:09:24
10-11-2020 08:29:55
A
6396
31

10-11-2020 09:09:34
10-11-2020 08:29:55
B
6409
31

10-11-2020 09:09:44
10-11-2020 08:29:55
B
6426
31

10-11-2020 09:10:24
10-11-2020 08:29:55
B
6442
31


WITH cte AS (
  SELECT DISTINCT BatchDate,
         SUM(MAX(Totaliser) - MIN(Totaliser)) OVER () ExpectedResult
  FROM (
    SELECT *, SUM(flag) OVER (PARTITION BY BatchDate ORDER BY LogDateAndTime) grp
    FROM (
      SELECT *, LAG(TagLetter, 1, '') OVER (PARTITION BY BatchDate ORDER BY LogDateAndTime) <> 'A' flag
      FROM tablename 
    ) t
    WHERE TagLetter = 'A'
  ) t
  GROUP BY BatchDate, grp
)
SELECT t.*, c.ExpectedResult
FROM tablename t LEFT JOIN cte c
ON c.BatchDate = t.BatchDate


LogDateAndTime
BatchDate
TagLetter
Totaliser
ExpectedResult


10-11-2020 09:06:14
10-11-2020 08:29:55
A
6319
31

10-11-2020 09:06:24
10-11-2020 08:29:55
A
6337
31

10-11-2020 09:08:14
10-11-2020 08:29:55
B
6355
31

10-11-2020 09:08:24
10-11-2020 08:29:55
B
6372
31

10-11-2020 09:08:34
10-11-2020 08:29:55
B
6378
31

10-11-2020 09:08:44
10-11-2020 08:29:55
A
6383
31

10-11-2020 09:09:14
10-11-2020 08:29:55
A
6388
31

10-11-2020 09:09:24
10-11-2020 08:29:55
A
6396
31

10-11-2020 09:09:34
10-11-2020 08:29:55
B
6409
31

10-11-2020 09:09:44
10-11-2020 08:29:55
B
6426
31

10-11-2020 09:10:24
10-11-2020 08:29:55
B
6442
31

LogDateAndTime	BatchDate	TagLetter	Totaliser	ExpectedResult
10-11-2020 09:06:14	10-11-2020 08:29:55	A	6319	31
10-11-2020 09:06:24	10-11-2020 08:29:55	A	6337	31
10-11-2020 09:08:14	10-11-2020 08:29:55	B	6355	31
10-11-2020 09:08:24	10-11-2020 08:29:55	B	6372	31
10-11-2020 09:08:34	10-11-2020 08:29:55	B	6378	31
10-11-2020 09:08:44	10-11-2020 08:29:55	A	6383	31
10-11-2020 09:09:14	10-11-2020 08:29:55	A	6388	31
10-11-2020 09:09:24	10-11-2020 08:29:55	A	6396	31
10-11-2020 09:09:34	10-11-2020 08:29:55	B	6409	31
10-11-2020 09:09:44	10-11-2020 08:29:55	B	6426	31
10-11-2020 09:10:24	10-11-2020 08:29:55	B	6442	31