0

Suppose I have this data:

  id  Start           End
---- -------------   ----------    
  1   2018-01-01     2018-01-31     
  1   2018-02-01     2018-02-28
  1   2018-03-01     2018-03-31
  1   2018-04-01     2018-04-30
  1   2018-08-01     2018-08-31
  1   2018-09-01     2018-09-30
  1   2018-10-01     2018-10-31

The data can be generated using this code:

df <- as.data.frame(cbind(
                      id = c(1, 1, 1, 1, 1, 1, 1), 
                
                      Start = c("2018-01-01", "2018-02-01", "2018-03-01", "2018-04-01", "2018-08-01", "2018-09-01", "2018-10-01"), 
                             
                      End =   c("2018-01-31", "2018-02-28", "2018-03-31", "2018-04-31", "2018-08-31", "2018-09-30", "2018-10-31")
                      ))

I want to compare each (N+1) with N row within the same id column, and decide whether combine the two rows or not based on a condition (if start (N+1 row) <= end +1 (N row)), for example:

Based on this logic then produce this new data:

  id  Start           End
---- -------------   ----------    
  1   2018-01-01     2018-04-30     
  1   2018-08-01     2018-10-31
Bernice
  • 79
  • 6
  • Which dbms are you using? – jarlh Sep 28 '22 at 18:32
  • the example of how to create the table is given in R but you want the solution in SQL, is that correct? Why don't you give the example of creating the data in SQL? – Hogan Sep 28 '22 at 18:35

0 Answers0