I have a problem that is a kin to the SQL "Exploding Parts" problem scenerio

[ageno]

I have a problem that is a kin to the SQL "Exploding Parts" problem scenerio

I have a table 300,000 rrecords, that is made up of the following fields:

Office - text
CallDate - Date
StartTime - Time
FinishTime - Time

A sample of the data is as follows:

Office1 9/3/02 3:00:09 PM 3:01:09 PM
Office1 9/3/02 3:00:18 PM 3:09:18 PM
Office1 9/3/02 3:01:12 PM 3:03:12 PM
Office1 9/3/02 3:01:15 PM 3:01:15 PM
Office1 9/3/02 3:02:00 PM 3:08:00 PM
Office1 9/3/02 3:02:12 PM 3:04:12 PM
Office1 9/3/02 3:02:57 PM 3:05:57 PM

This data is telephone calls. I am attempting to determine that when a call starts, how many other calls are occuring during the period of that calls starttime and endtime. Basically, concurrent calls.

In essense each record, needs to query a subset of the entire recordset, only if the primary record equals the office and date, there by reducing the entire recordset of 300,000, down to a more manageable set, that is relevant.

Any assistance is greatly appreciated.


Gene.

 

[FoFa]

How about something like:
Select yadda yadda yadda
WHERE office = (some office value)
AND calldate = (some call date)
AND (starttime between (some start time) AND (some end time)
OR endtime between (same start time) AND (same end time))

 

[ageno]

Thanks, I am trying that however, the main objective is that I would like to know how many other concurrent calls, count of records, that meet the criteria through the between statements are going on, during the interval starttime through endtime for each calll.

Not sure if I am am clearly articulating the final result.

For example. Assuming a small sample set, say 10 records (calls), The first call, after review of the other 9, show that the during the interval of start to end, there was a maximum, of 3 other calls going on at some point during the 1st calls start and end, I would expect a summed up count of 4, the first call itself, and the other 3 calls that also occured,during that window.

Most other folks I have spoke with don't feel it can be done in SQL, because of the recursion, that each call needs to compare against the complete remaining recordset.

However, I have not given up ........

 

[Nouba]

I think that is what you're looking for

SELECT
  C.Office
, C.CallDate
, C.StartTime
, C.FinishTime
, (SELECT
    Count(*)
  FROM tblCalls
  WHERE
    Office=C.Office AND
    StartTime BETWEEN C.StartTime AND C.FinishTime)-1 AS CntOfOtherCalls
FROM tblCalls AS C

 

[ageno]

Sweet!, That did it, a few tweaks to get it the way I need it. Persistance pays off. Thanks to all.