Query Construction problem

[Dembrey]

Hi,

I'm having difficulty constructing a query for the following problem, anyone know how I can go about this...

I have a table which records events on particular objects, with 3 fields:
ObjectName
StartTime
EndTime

If the StartTime of an event falls within 10mins of the EndTime of another event on the same object I need to combine these two events into one, taking the StartTime of the 1st event and the EndTime of the second.

In practice this would result in starting with the following data:

Object1 10:00 10:23
Object1 11:41 13:57
Object2 12:07 12:45
Object2 12:48 13:17
Object2 18:33 19:11
etc.

Would result in the following output:

Object1 10:00 10:23
Object1 11:41 13:57
Object2 12:07 13:17 <= StartTime from 1st event, EndTime from 2nd
Object2 18:33 19:11

I'm pretty sure I can do this by VB code, but I'd rather do it by query if its possible. Any ideas?

Thanks.

 

[doco]

Order By 2, 3

 

[Dembrey]

doco,

Thanks for the reply but I'm not sure I follow what you mean.

The outputted query should combine two records into one (taking the StartTime from the first and the EndTime from the second), I don't see how an ORDER BY clause will acheive this.

thanks.

 

[dallr]

I would create a primary key (automnumber) within your table. I called this autonumber ObjectID in the following sql.

I called my base table Yourtable. So in the following SQL wherever you see the table "yourtable" replace it with the name of yours. I have also attached a sample db.

SELECT T1.ObjectName
     , T1.ObjectID
     , T1.StartTime
     , IIf(DateDiff("n",endtime,(SELECT T2.StartTime FROM YourTable T2 WHERE T2.ObjectID =( T1.objectID + 1)AND T2.ObjectName = T1.ObjectName))<=10,
                         (SELECT T2.EndTime FROM YourTable T2 WHERE T2.ObjectID =( T1.objectID + 1) AND T2.ObjectName = T1.ObjectName),EndTime) AS EndTime_New
FROM Yourtable AS T1 
     LEFT JOIN 
             (SELECT Max(T4.ObjectID) AS MaxOfObjectID
             FROM (SELECT T1.ObjectName
                           , T1.ObjectID
                           , IIf(DateDiff("n",endtime,(SELECT T2.StartTime FROM YourTable T2 WHERE T2.ObjectID =( T1.objectID + 1)AND T2.ObjectName = T1.ObjectName))<=10,
                                         (SELECT T2.EndTime FROM YourTable T2 WHERE T2.ObjectID =( T1.objectID + 1) AND T2.ObjectName = T1.ObjectName),EndTime) AS EndTime_New
                  FROM Yourtable AS T1) AS T4
             GROUP BY T4.ObjectName, T4.EndTime_New
             HAVING (((Count(T4.ObjectID))>1))) T4
        ON T1.ObjectID = T4.MaxOfObjectID
WHERE T4.MaxOfObjectID IS NULL;

Dallr

 

[doco]

Order By 2, 3

Aha! I misread your question and it looked to me as though you were trying to order. Guess I should have coffee before I answer.

 

[dallr]

Question: What suppose to happen when two objects are in succession and have times within 10 minutes?

Dallr

 

[Dembrey]

Whoa

I don't feel so bad about having to ask for help now....

Many thanks Dallr, that really is a quite impressive solution!

Cheers.

 

[dallr]

I can provide another solution which uses the same fields you provided but without the autonumber primary key (ObjectID). However, it depends on the last question I asked.

What suppose to happen when two objects are in succession and have times within 10 minutes?

dallr

 

[Dembrey]

Dallr,

In fact the solution query is a sort of summary view. If the events (which are actually based on orbital mechanics) are so close they must have the same root cause and must be treated as a single "happening" for other post processing I need to do.

Thanks once again.

 

[dallr]

OK, once you are happy.

Dallr

 

[Dembrey]

Dallr,

Misread your question.

Events of different objects are never to be combined regardless of the proximity of their timing.

Cheers.

 

[dallr]

Maybe it me who did not phrase the question correctly.
What I was asking is, what happens when two of the same objects have records in sequence whereby the end time of one record is within 10 minutes of the next record.

Example
Object1 10:00 10:23
Object1 11:41 13:57
Object2 12:07 12:45
Object2 12:48 13:17
Object2 13:20 15:03
Object2 18:33 19:11


In light of what I explained in my first paragraph. If the records are like above the results should be this:

Results Sample 1
Object1 10:00 10:23
Object1 11:41 13:57
Object2 12:07 13:17 *
Object2 12:48 15:03 *
Object2 18:33 19:11

Or this
Results Sample 2
Example
Object1 10:00 10:23
Object1 11:41 13:57
Object2 12:07 15:03 *
Object2 18:33 19:11

Dallr

 

[Dembrey]

Dallr,

It's not possible to have more than two events of one object occuring so close together. If the 2nd event follows the 1st by <10mins then the 3rd event on that object will be at least 12 hrs after the end of the 2nd one.

If that opens the possibility of another solution I'd be more than happy to have your ideas.

Cheers.

 

[dallr]

The code I provided should handle it without a problem. I was just asking so that we covered all possible scenarios of how the data might be stored.

Glad to assist. I am out!

Dallr