Which table and field names do you want in the WHERE clauses? If it is
in FIRE_INCIDENT_BASE_INFORMATION you have to add the (NOT) EXISTS in
the ON clause, if it is in APPARATUSRESPONSE then just add the code in
the WHERE clause.

Guessing a bit (you don't select any DATE fields from
FIRE_INCIDENT_BASE_INFORMATION):

SELECT
H.ID,
H.FDID,
H.LOCATION_ID,
H.INCIDENTNO,
H.INCIDENTTYPE,
H.EXPOSURE,
T.INSERVICEDATE,
T.ENROUTEDATE,
T.CLEARDATE,
T.APPARATUSID,
T.ARRIVALDATE,
CAST(24*(T.ARRIVALDATE-T.ENROUTEDATE) AS INTEGER),
CAST((((T.ARRIVALDATE-T.ENROUTEDATE)*24)-CAST((T.ARRIVALDATE-
T.ENROUTEDATE)*24 AS INTEGER)) * 60 AS INTEGER)
FROM FIRE_INCIDENT_BASE_INFORMATION H RIGHT JOIN APPARATUSRESPONSE T
ON H.ID = T.ID
WHERE EXISTS(
SELECT * FROM APPARATUSRESPONSE T2
WHERE T2.ID = T.ID
AND T2.ARRIVALDATE < T.ARRIVALDATE)
AND NOT EXISTS(
SELECT * FROM APPARATUSRESPONSE T3
JOIN APPARATUSRESPONSE T4 ON T3.ID = T4.ID
WHERE T3.ID = T.ID
AND T3.ARRIVALDATE < T.ARRIVALDATE
AND T4.ARRIVALDATE < T3.ARRIVALDATE)

HTH,
Set

legacydrum wrote:

> Set,
> Thanks for your reply. That seems to work for the one table.
> Hoever, after looking at it further that is the detail table and not
> the master. I need to add the SQL code you mentioned below to the
> following view. Do you have any ideas on how to do this?
>
> CREATE VIEW SECONDAPPARATUSARRIVAL(
> ID,
> FDID,
> LOCATION_ID,
> INCIDENTNO,
> INCIDENTTYPE,
> EXPOSURE,
> INSERVICEDATE,
> ENROUTEDATE,
> CLEARDATE,
> APPARATUSID,
> ARRIVALDATE,
> DIFFHRS,
> DIFFMIN)
>
> AS
> SELECT
> H.ID,
> H.FDID,
> H.LOCATION_ID,
> H.INCIDENTNO,
> H.INCIDENTTYPE,
> H.EXPOSURE,
> T.INSERVICEDATE,
> T.ENROUTEDATE,
> T.CLEARDATE,
> T.APPARATUSID,
> T.ARRIVALDATE,
> CAST(24*(T.ARRIVALDATE-T.ENROUTEDATE) AS INTEGER),
> CAST((((T.ARRIVALDATE-T.ENROUTEDATE)*24)-CAST((T.ARRIVALDATE-
> T.ENROUTEDATE)*24 AS INTEGER)) * 60 AS INTEGER)
>
> FROM FIRE_INCIDENT_BASE_INFORMATION H RIGHT JOIN APPARATUSRESPONSE T
> ON H.ID = T.ID;
>
> Thanks again for your help,
>
> Brad
>
>
> --- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
> <svein.erling.tysvaer@...> wrote:
>> Hi Brad!
>>
>> Another way to think of your problem, is that there should exist
> one,
>> but but not two records with a prior datetime. Assuming there are
> no
>> duplicate datetimes, you could then do
>>
>> SELECT M1.ID, M1.MYDATETIME
>> FROM MYTABLE M1
>> WHERE EXISTS(
>> SELECT * FROM MYTABLE M2
>> WHERE M2.ID = M1.ID
>> AND M2.MYDATETIME < M1.MYDATETIME)
>> AND NOT EXISTS(
>> SELECT * FROM MYTABLE M3
>> JOIN MYTABLE M4 ON M3.ID = M4.ID
>> WHERE M3.ID = M1.ID
>> AND M3.MYDATETIME < M1.MYDATETIME
>> AND M4.MYDATETIME < M3.MYDATETIME)
>>
>> HTH,
>> Set
>>
>> legacydrum wrote:
>>> I am in need of some SQL help. I have a detailed table with
> multiple
>>> records for each ID with date/times as listed below.
>>>
>>> ID DATETIME
>>> 2 1/14/2007 9:18:26 AM
>>> 2 1/14/2007 9:20:10 AM
>>> 2 1/14/2007 9:21:17 AM
>>> 2 1/14/2007 9:23:05 AM
>>> 3 1/20/2007 12:07:15 AM
>>> 3 1/20/2007 12:09:19 AM
>>> 3 1/20/2007 12:15:45 AM
>>> 3 1/20/2007 12:22:50 AM
>>>
>>> I need to be able to query the data and return the second
> greatest
>>> time for each ID which would result as follows.
>>>
>>> ID DATETIME
>>> 2 1/14/2007 9:20:10 AM
>>> 3 1/20/2007 12:09:19 AM
>>>
>>> If it were the first or last record, I could use SELECT MAX.
>>> However, I am not sure how to do this. Any idea's would be
> greatly
>>> appreciated.
>>>
>>> Sincerely,
>>>
>>> Brad