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

Considering blank spaces

I need to make a function for coding string in which i make difference
between strings that are identical except from final blank spaces. In SQL 20
0
exists an option (collate set or something else) that can make SQL
distinguish betwene two string like these:
'MYSTRING1'
'MTSTRING1 '
ThanksOne method...
if(cast('as ' as varbinary) = cast('as' as varbinary))
print 'match'
else
print 'does not match'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Try this:
USE tempdb
GO
CREATE FUNCTION MyStringCompare(@.str1 nvarchar(MAX), @.str2 nvarchar(MAX))
RETURNS nvarchar(15)
AS
BEGIN
IF ((@.str1 + N'#') = (@.str2 + N'#'))
RETURN N'match'
RETURN N'don''t match'
END
GO
SELECT tempdb.dbo.MyStringCompare(N'MYSTRING1', N'MYSTRING1') -- -> match
SELECT tempdb.dbo.MyStringCompare(N'MYSTRING1', N'MYSTRING1 ') -- -> don't
match
SELECT tempdb.dbo.MyStringCompare(null, N'MYSTRING1 ') -- -> don't match
SELECT tempdb.dbo.MyStringCompare(N'MYSTRING1', null) -- -> don't match
SELECT tempdb.dbo.MyStringCompare(null, null) -- -> don't match
Greetings,
Urs
"checcouno" wrote:

> I need to make a function for coding string in which i make difference
> between strings that are identical except from final blank spaces. In SQL
200
> exists an option (collate set or something else) that can make SQL
> distinguish betwene two string like these:
> 'MYSTRING1'
> 'MTSTRING1 '
> Thanks|||How about this:
declare @.a varchar(30)
declare @.b varchar(30)
set @.a = 'MYSTRING1'
set @.b = 'MTSTRING1 '
IF (@.a = @.b AND DATALENGTH(@.a) = DATALENGTH(@.b))
PRINT 'Matched'
ELSE
PRINT 'Different'
Roy Harvey
Beacon Falls, CT
On Fri, 30 Jun 2006 00:26:01 -0700, checcouno
<checcouno@.discussions.microsoft.com> wrote:

>I need to make a function for coding string in which i make difference
>between strings that are identical except from final blank spaces. In SQL 2
00
>exists an option (collate set or something else) that can make SQL
>distinguish betwene two string like these:
>'MYSTRING1'
>'MTSTRING1 '
>Thanks|||>> I need to make a function for coding string in which make difference betw
een strings that are identical except from final blank spaces. <<
Be very careful about this. SQL pads shorter strings with blanks for
comparing them, so your function could destroy expected behavior and
you would wind up with your own private language. Otherwise, use the
DATALENGTH() function; it is standard and portable.