tricky sql challenge

OK, SQL wizards, a question to ponder:

Can you come up with a single SQL update statement to solve the following problem?

We need to determine the maximum time window of a series of seismic events.

In the case of a seismic event (earthquake, explosion), a single event occurs at a given location at a certain time. However, the event may be recorded at several monitoring stations around the world at different times. This, due to a combination of geology and physics. What we need to do is create a single record that shows the maximum time window of the event.

A single event is characterized by a series of time values that overlap. When there is no overlap, a new event is defined.

Given the following table schema:

	eventid		unique event identifier key
	beg_time	epoch time value of start of event
	end_time	epoch time value of end of event

A simplified example of the data:

	eventid		beg_time	end_time


	-------		---------	---------

	10001		734000000	734001000
	10002		734000050	734001500
	10003		734000450	734002000
	10004		734006000	734007000
	10005		734006500	734008000
	10006		734009000	734009500
	10007		734009100	734009600
	10008		734009350	734009750

What we want to create is a display of single events based on the time overlap.

Graphically, the events would show up similar to this:

10008                                                  ===
10007                                                 ===
10006                                                ===
10005                                    ========
10004                                 ====== 
10003       =======
10002    =======
10001	======
time	+----+----+----+----+----+----+----+----+----+----+----
    734000000                734005000                 734010000

This graph plainly shows three distinct events.

What we would like to have is a table that reflects the minimum start time and the maximum end time. The row that would be updated would be the row with the smallest start time of the group.

The updated table would look like:

	eventid		beg_time	end_time


	-------		---------	---------

	10001		734000000	734002000
	10002		734000050	734001500
	10003		734000450	734002000
	10004		734006000	734008000
	10005		734006500	734008000
	10006		734009000	734009750
	10007		734009100	734009600
	10008		734009350	734009750

The rows for eventid 10002,10003,10005,10007 and 10008 would be purged after the update.

If anyone can come up a nifty solution to this, I would be much obliged.

Let the games begin.

Thanks,

Dale Cooper
Center for Seismic Studies
Arlington, VA Received on Tue May 03 1994 - 22:15:32 CEST