Considering only first arrival

I am have a report that compiles response times for first responders which
works fine. But what I need is to be able to only consider the responding
units that get to the scene first. Example. Currently if 2 units are
dispatched and one gets on scene in 4 mins and the other in 6 mins, our
response time is 5 mins, which is not true since we were there in 4 mins. In
other words, I need to be able to ignore the 2nd, 3rd etc arriving units.
The reportNumber is the key.
ReportNumber, Unit_ID, Arrival_Time
Thanks.Cant imaging what you mean without DDL or /and sample data.
Jens.
http://www.sqlserver2005.de
--
"Dave S." <davidstedman@.colliergov.net> schrieb im Newsbeitrag
news:OlS65SQQFHA.2580@.TK2MSFTNGP10.phx.gbl...
>I am have a report that compiles response times for first responders which
> works fine. But what I need is to be able to only consider the responding
> units that get to the scene first. Example. Currently if 2 units are
> dispatched and one gets on scene in 4 mins and the other in 6 mins, our
> response time is 5 mins, which is not true since we were there in 4 mins.
> In
> other words, I need to be able to ignore the 2nd, 3rd etc arriving units.
> The reportNumber is the key.
> ReportNumber, Unit_ID, Arrival_Time
> Thanks.
>|||Try,
select ReportNumber, Unit_ID, Arrival_Time
from table1
where Arrival_Time = (select min(a.Arrival_Time) from table1 as a where
a.ReportNumber = table1.ReportNumber)
AMB
"Dave S." wrote:

> I am have a report that compiles response times for first responders which
> works fine. But what I need is to be able to only consider the responding
> units that get to the scene first. Example. Currently if 2 units are
> dispatched and one gets on scene in 4 mins and the other in 6 mins, our
> response time is 5 mins, which is not true since we were there in 4 mins.
In
> other words, I need to be able to ignore the 2nd, 3rd etc arriving units.
> The reportNumber is the key.
> ReportNumber, Unit_ID, Arrival_Time
> Thanks.
>
>|||This sorta works except that it appears to not return records where only one
unit responded, which is most of the time..:(
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5BCF21B6-B932-4807-B96E-3FA61A36420D@.microsoft.com...
> Try,
> select ReportNumber, Unit_ID, Arrival_Time
> from table1
> where Arrival_Time = (select min(a.Arrival_Time) from table1 as a where
> a.ReportNumber = table1.ReportNumber)
>
> AMB
> "Dave S." wrote:
>
which
responding
mins. In
units.|||On Fri, 15 Apr 2005 09:22:40 -0400, Dave S. wrote:

>This sorta works except that it appears to not return records where only on
e
>unit responded, which is most of the time..:(
Hi Dave,
Strange. I used the following script to test Alejandro's suggestion and
it worked for me. Could you post a script to show me under what
circumstances you don't get the rows you expect?
-- Make the table
CREATE TABLE table1
(ReportNumber int NOT NULL,
Unit_ID int NOT NULL,
Arrival_Time datetime NOT NULL,
PRIMARY KEY (ReportNumber, Unit_ID) -- just guessing
)
go
-- Put in some data.
-- Report 1 has two rows; reports 2 and 3 have one row each.
INSERT INTO table1 (ReportNumber, Unit_ID, Arrival_Time)
SELECT 1, 1, dateadd(minute, -3, getdate())
UNION ALL
SELECT 1, 2, dateadd(minute, -2, getdate())
UNION ALL
SELECT 2, 1, dateadd(minute, -1, getdate())
UNION ALL
SELECT 3, 2, dateadd(minute, -4, getdate())
go
-- Alejandro's code - returns one row for each report
select ReportNumber, Unit_ID, Arrival_Time
from table1
where Arrival_Time = (select min(a.Arrival_Time) from table1 as a where
a.ReportNumber = table1.ReportNumber)
go
-- Done - clean up my test DB.
DROP TABLE table1
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||This only returns a single record. Is it supposed to work that way?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5BCF21B6-B932-4807-B96E-3FA61A36420D@.microsoft.com...
> Try,
> select ReportNumber, Unit_ID, Arrival_Time
> from table1
> where Arrival_Time = (select min(a.Arrival_Time) from table1 as a where
> a.ReportNumber = table1.ReportNumber)
>
> AMB
> "Dave S." wrote:
>
which
responding
mins. In
units.sqlsql