'Select records and group each time column changes

I have this (MySQL) database, sorted by increasing timestamp:

Timestamp     RateSingle    RateDouble    RoomId
2022-01-01     100            150           1     
2022-01-02     100            150           1     
2022-01-03     150            200           1
2022-01-04     150            200           1
2022-01-05     200            250           1      
2022-01-06     200            250           1      
2011-01-07     300            350           1

What I am trying to do is group the Timestamp and select rates each time RateSingle changes.

Expected result

      Timestamp          RateSingle    RateDouble    
2022-01-01 - 2022-01-02    100            150               
2022-01-03 - 2022-01-04    150            200           
2022-01-05 - 2022-01-06    200            250                
2011-01-07                 300            350

My query so far works only partly:

SELECT a.*
FROM `room_rates` AS a
WHERE a.RateSingle <>
      ( SELECT b.RateSingle
        FROM `room_rates` AS b
        WHERE a.RoomId= b.RoomId
          AND a.Timestamp> b.Timestamp
        ORDER BY b.Timestamp DESC
        LIMIT 1
      );
mysql


Solution 1:[1]

Is this what you are looking for?:

library(dplyr)

df %>% 
  group_by(RateSingle) %>% 
  mutate(Timestamp = paste(Timestamp, lead(Timestamp), sep = " - ")) %>% 
  slice(1) %>% 
  ungroup()

  Timestamp               RateSingle RateDouble RoomId
  <chr>                        <int>      <int>  <int>
1 2022-01-01 - 2022-01-02        100        150      1
2 2022-01-03 - 2022-01-04        150        200      1
3 2022-01-05 - 2022-01-06        200        250      1
4 2011-01-07 - NA                300        350      1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 TarJae

Related Questions