【Question】

Source data:

xh    num1    num2

1          10015    60080

2          20022    80090

3          30250    40900

Find overlapped data intervals for each row to get new records:

xh    num1    num2

1          10015    30080

2          20022    50090

3   60250    90900
Merge overlapped data to get this:

xh    num1    num2

1         10015    50090

2   60250    90900

【Answer】

To merge data, check each row beginning from the second one to see if it includes data from the last one to form a new interval. They will be handled as one interval if it does and a new interval will be created if it doesn’t. Then get the smallest data and the largest data for each interval. To do this in SQL, we need a need a nested query. That’s a hassle. But it’s easy to get it done in SPL. Here’s SPL script:

A1: Get data in SQL ordered by num1;

A2: Grouped rows containing overlapped data;

A3: Create a new table sequence according to the grouped rows and populate the smallest value the largest value in each group to num1 and num2 respectively.

Here’s the desired result: