Can this be done with a query?

  • Thread starter Thread starter UlfG
  • Start date Start date
U

UlfG

Good day,

I have a communication software that receives calls on multiple lines
and with various call lengths. The software logs all activities in a
table that looks like this:

Date length (s) Port
......................................
2003-09-11 10:56:00 30 1
2003-09-11 10:56:10 25 4
2003-09-11 10:56:17 46 6
2003-09-11 10:56:50 18 1
2003-09-11 10:59:45 25 1

I would like to know wich ports that are active during a call. In the
sample data above, one can see that 2 other ports besides port 1 was
used during the first call, same for second call and so on.

Can this be done with a query? Any suggestions is highly appriciated!

TIA,
Ulf
 
Hi,



Assuming Starting time is < Ending time, then interval [S1, E1] do
not overlap in any way interval [S2, E2] if, and only if,

E2 < S1 or E1 < S2

(by evidence, since that express the fact that one message end before the
other start).

If you want an overlap, you logically negate the previous condition, leaving
( De Morgan law ) :

overlap if and only if: E2>= S1 AND E1 >= S2 ( less
evident, but you can try, by hand, with two strips of paper, and convince
yourself that, indeed, that is the applicable criteria).


So, the following query will "match" the overlapping calls. I used strict >
(not >=) to avoid matching itself with itself, and further more, at the
exact second one end, the other start, does not necessary qualify for an
overlap, when time is concerned, generally.


SELECT a.*, b.*
FROM MyTable AS a INNER JOIN MyTable AS b
ON ( b.[Date] + b.Length > a.[Date]
AND
a.[Date]+a.Length > b.[Date] )


since ending, E, is [Date] + length, and starting, S, is just [Date], with
the fields name you used.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top