MySQL 8.0

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

version()
8.0.30

WITH 
    data_new (CREATED_DATE, RAKE_DEVICE, ROUNDED_GEO_LAT, ROUNDED_GEO_LNG, IDLE_OR_MOVING) AS
        ( Select STR_TO_DATE('2024-08-01 11:27:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
          Select STR_TO_DATE('2024-08-01 10:27:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
          Select STR_TO_DATE('2024-08-01 09:27:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
          Select STR_TO_DATE('2024-08-01 08:27:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
          Select STR_TO_DATE('2024-08-01 07:27:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
          Select STR_TO_DATE('2024-08-01 06:27:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'moving' From Dual Union All
          Select STR_TO_DATE('2024-08-01 05:27:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.28, 76.39, 'moving' From Dual Union All
          Select STR_TO_DATE('2024-08-01 05:21:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
          Select STR_TO_DATE('2024-08-01 05:12:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
          Select STR_TO_DATE('2024-08-01 04:12:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
          Select STR_TO_DATE('2024-08-01 03:12:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.29, 76.39, 'idle' From Dual Union All
          Select STR_TO_DATE('2024-08-01 02:12:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.24, 76.39, 'moving' From Dual Union All
          Select STR_TO_DATE('2024-08-01 03:13:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.24, 76.39, 'moving' From Dual Union All
          Select STR_TO_DATE('2024-08-01 03:09:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.245, 76.33129, 'moving' From Dual Union All
          Select STR_TO_DATE('2024-08-01 05:12:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.324, 76.3921, 'idle' From Dual Union All
          Select STR_TO_DATE('2024-08-01 05:12:00', '%Y-%m-%d %H:%i:%s'), 'a-b', 28.324, 76.3921, 'idle' From Dual 
        )
SELECT    a.CREATED_DATE, a.RAKE_DEVICE, a.ROUNDED_GEO_LAT, a.ROUNDED_GEO_LNG,
          a.IDLE_OR_MOVING,
          Case When a.LAT_LNG = a.PREV_LAT_LNG And a.LAT_LNG = a.NEXT_LAT_LNG
               Then 'idle'
          Else a.IDLE_OR_MOVING
          End "NEW_IDLE_MOVING_WITH_AND",
          Case When a.LAT_LNG = a.PREV_LAT_LNG Or a.LAT_LNG = a.NEXT_LAT_LNG
               Then 'idle'
          Else a.IDLE_OR_MOVING
          End "NEW_IDLE_MOVING_WITH_OR",
          a.DATE_START, a.DATE_END
FROM
  ( Select CREATED_DATE, RAKE_DEVICE, ROUNDED_GEO_LAT, ROUNDED_GEO_LNG, 
           IDLE_OR_MOVING,
           Concat(ROUNDED_GEO_LAT, ' - ', ROUNDED_GEO_LNG) "LAT_LNG",
           Concat(Coalesce(Lag(ROUNDED_GEO_LAT) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LAT), 
                  ' - ',
                  Coalesce(Lag(ROUNDED_GEO_LNG) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LNG)
           ) "PREV_LAT_LNG", 
           Concat(Coalesce(Lead(ROUNDED_GEO_LAT) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LAT), 
                  ' - ',
                  Coalesce(Lead(ROUNDED_GEO_LNG) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LNG)
           ) "NEXT_LAT_LNG",
           Coalesce(Lag(ROUNDED_GEO_LAT) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LAT) "LAT_PREV",
           Coalesce(Lead(ROUNDED_GEO_LAT) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LAT) "LAT_NEXT",
           Coalesce(Lag(ROUNDED_GEO_LNG) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LNG) "LNG_PREV",
           Coalesce(Lead(ROUNDED_GEO_LNG) Over(Order By CREATED_DATE Desc), ROUNDED_GEO_LNG) "LNG_NEXT",
           Min(CREATED_DATE) Over(Partition By Concat(ROUNDED_GEO_LAT, ' - ', ROUNDED_GEO_LNG)) "DATE_START", 
           Max(CREATED_DATE) Over(Partition By Concat(ROUNDED_GEO_LAT, ' - ', ROUNDED_GEO_LNG)) "DATE_END"
    From      data_new
    Order By  CREATED_DATE Desc, ROUNDED_GEO_LAT, ROUNDED_GEO_LNG
  ) a

CREATED_DATE	RAKE_DEVICE	ROUNDED_GEO_LAT	ROUNDED_GEO_LNG	IDLE_OR_MOVING	NEW_IDLE_MOVING_WITH_AND	NEW_IDLE_MOVING_WITH_OR	DATE_START	DATE_END
2024-08-01 11:27:00	a-b	28.290	76.39000	idle	idle	idle	2024-08-01 03:12:00	2024-08-01 11:27:00
2024-08-01 10:27:00	a-b	28.290	76.39000	idle	idle	idle	2024-08-01 03:12:00	2024-08-01 11:27:00
2024-08-01 09:27:00	a-b	28.290	76.39000	idle	idle	idle	2024-08-01 03:12:00	2024-08-01 11:27:00
2024-08-01 08:27:00	a-b	28.290	76.39000	idle	idle	idle	2024-08-01 03:12:00	2024-08-01 11:27:00
2024-08-01 07:27:00	a-b	28.290	76.39000	idle	idle	idle	2024-08-01 03:12:00	2024-08-01 11:27:00
2024-08-01 06:27:00	a-b	28.290	76.39000	moving	moving	idle	2024-08-01 03:12:00	2024-08-01 11:27:00
2024-08-01 05:27:00	a-b	28.280	76.39000	moving	moving	moving	2024-08-01 05:27:00	2024-08-01 05:27:00
2024-08-01 05:21:00	a-b	28.290	76.39000	idle	idle	idle	2024-08-01 03:12:00	2024-08-01 11:27:00
2024-08-01 05:12:00	a-b	28.290	76.39000	idle	idle	idle	2024-08-01 03:12:00	2024-08-01 11:27:00
2024-08-01 05:12:00	a-b	28.324	76.39210	idle	idle	idle	2024-08-01 05:12:00	2024-08-01 05:12:00
2024-08-01 05:12:00	a-b	28.324	76.39210	idle	idle	idle	2024-08-01 05:12:00	2024-08-01 05:12:00
2024-08-01 04:12:00	a-b	28.290	76.39000	idle	idle	idle	2024-08-01 03:12:00	2024-08-01 11:27:00
2024-08-01 03:13:00	a-b	28.240	76.39000	moving	moving	moving	2024-08-01 02:12:00	2024-08-01 03:13:00
2024-08-01 03:12:00	a-b	28.290	76.39000	idle	idle	idle	2024-08-01 03:12:00	2024-08-01 11:27:00
2024-08-01 03:09:00	a-b	28.245	76.33129	moving	moving	moving	2024-08-01 03:09:00	2024-08-01 03:09:00
2024-08-01 02:12:00	a-b	28.240	76.39000	moving	moving	idle	2024-08-01 02:12:00	2024-08-01 03:13:00