RaqForum 28 No.
572 View  • 
     ...

How to Merge Overlapping Time Periods

 
SQL(179)

【Question】

My table:

startTime        endTime
2015-01-02      2015-02-03
2015-01-10      2015-02-05
2015-01-03      2015-01-04
2015-03-01      2015-03-05
The result I want:
startTime       endTime
2015-01-02     2015-02-05
2015-03-01     2015-03-05

 

【Answer】

It’s clearer and simpler to implement this computation in SPL than with window functions in Java plus SQL:

A

B

1

$select startTime,endTime   from tb order  by startTime

2

=A1.group@i(startTime>max(endTime[,-1]))

3

=A2.new(~.min(startTime):startTime,~.max(endTime):endTime)

A1: Retrieve data in SQL and sort data by startTime;

A2: Put records containing overlapping time periods into one group;

A3: Create a new table sequence where the startTime is the smallest value in each group and the endTime is the biggest one in the group.

An SPL script can be integrated with another application via esProc JDBC. For details, see How to Call an SPL Script in Java

 


SQL(179)
 
 •  572 View  •