Query help

I have a table with one column that has all negative values.
IO
-70.72
-72.93
-67.38
-65.05
-62.31
-65.52
-69.98
now we have to find that how many samples of IO are between
-50 to -40 range
-60 to -70 range
-70 to -60 range
How can I accomplish this. Thanks for your help.This should give you something to think about.
SELECT CONVERT(int, ThatColumn / 10) * 10 as RangeStart,
count(*) as Rows
FROM TheTable
GROUP BY CONVERT(int, ThatColumn / 10)
ORDER BY RangeStart
Roy Harvey
Beacon Falls, CT
On Fri, 7 Sep 2007 10:00:08 -0700, Shan
<Shan@.discussions.microsoft.com> wrote:
>I have a table with one column that has all negative values.
>IO
>-70.72
>-72.93
>-67.38
>-65.05
>-62.31
>-65.52
>-69.98
>now we have to find that how many samples of IO are between
>-50 to -40 range
>-60 to -70 range
>-70 to -60 range
>How can I accomplish this. Thanks for your help.|||This doesn't work. Any other ideas.
"Shan" wrote:
> I have a table with one column that has all negative values.
> IO
> -70.72
> -72.93
> -67.38
> -65.05
> -62.31
> -65.52
> -69.98
> now we have to find that how many samples of IO are between
> -50 to -40 range
> -60 to -70 range
> -70 to -60 range
> How can I accomplish this. Thanks for your help.|||On Fri, 7 Sep 2007 15:02:02 -0700, Shan
<Shan@.discussions.microsoft.com> wrote:
>This doesn't work. Any other ideas.
CREATE TABLE TestRanges (d decimal (13,2) NOT NULL)
INSERT TestRanges VALUES (-70.72)
INSERT TestRanges VALUES (-72.93)
INSERT TestRanges VALUES (-67.38)
INSERT TestRanges VALUES (-65.05)
INSERT TestRanges VALUES (-62.31)
INSERT TestRanges VALUES (-65.52)
INSERT TestRanges VALUES (-69.98)
SELECT CONVERT(int, d / 10) * 10 as RangeStart,
count(*) as Rows
FROM TestRanges
GROUP BY CONVERT(int, d / 10)
ORDER BY RangeStart
RangeStart Rows
-- --
-70 2
-60 5
Is this not close?
Roy Harvey
Beacon Falls, CT

Query blocked by negative spid

I'm running SQL 2K SP3, and I have a query that hangs and when I check the
sysprocesses table, the process is being blocked by spid -2. Also, when I
run sp_lock, there are several locks on the table I'm querying that have a
spid of -2. There are no errors in the errorlog.
There is no process with a spid = -2, so I don't have anything to kill. How
can I get rid of this without rebooting the SQL Server?
>
> I'm running SQL 2K SP3, and I have a query that hangs and when I check
the
> sysprocesses table, the process is being blocked by spid -2. Also, when
I
> run sp_lock, there are several locks on the table I'm querying that have
a
> spid of -2. There are no errors in the errorlog.
> There is no process with a spid = -2, so I don't have anything to kill.
How
> can I get rid of this without rebooting the SQL Server?
From SQL Server Books Online:
In SQL Server 2000, all orphaned DTC transactions are assigned the SPID
value of '-2'. Orphaned DTC transactions are distributed transactions that
are not associated with any SPID. Thus, when an orphaned transaction is
blocking another process, this orphaned distributed transaction can be
identified by its distinctive '-2' SPID value. For more information, see
KILL.
UOW
Identifies the Unit of Work ID (UOW) of the DTC transaction. UOW is a
character string that may be obtained from the syslockinfo table, which
gives the UOW for every lock held by a DTC transaction. UOW also may be
obtained from the error log or through the DTC monitor. For more
information on monitoring distributed transactions, see the MS DTC user
manual.
Use KILL UOW to terminate orphaned DTC transactions, which are not
associated with any real SPID and instead are associated artificially with
SPID = '-2'. For more information on SPID = '-2', see the Remarks section
later in this topic.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Query blocked by negative spid

I'm running SQL 2K SP3, and I have a query that hangs and when I check the
sysprocesses table, the process is being blocked by spid -2. Also, when I
run sp_lock, there are several locks on the table I'm querying that have a
spid of -2. There are no errors in the errorlog.
There is no process with a spid = -2, so I don't have anything to kill. How
can I get rid of this without rebooting the SQL Server?>
> I'm running SQL 2K SP3, and I have a query that hangs and when I check
the
> sysprocesses table, the process is being blocked by spid -2. Also, when
I
> run sp_lock, there are several locks on the table I'm querying that have
a
> spid of -2. There are no errors in the errorlog.
> There is no process with a spid = -2, so I don't have anything to kill.
How
> can I get rid of this without rebooting the SQL Server?
--
From SQL Server Books Online:
In SQL Server 2000, all orphaned DTC transactions are assigned the SPID
value of '-2'. Orphaned DTC transactions are distributed transactions that
are not associated with any SPID. Thus, when an orphaned transaction is
blocking another process, this orphaned distributed transaction can be
identified by its distinctive '-2' SPID value. For more information, see
KILL.
UOW
Identifies the Unit of Work ID (UOW) of the DTC transaction. UOW is a
character string that may be obtained from the syslockinfo table, which
gives the UOW for every lock held by a DTC transaction. UOW also may be
obtained from the error log or through the DTC monitor. For more
information on monitoring distributed transactions, see the MS DTC user
manual.
Use KILL UOW to terminate orphaned DTC transactions, which are not
associated with any real SPID and instead are associated artificially with
SPID = '-2'. For more information on SPID = '-2', see the Remarks section
later in this topic.
Hope this helps,
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Query blocked by negative spid

I'm running SQL 2K SP3, and I have a query that hangs and when I check the
sysprocesses table, the process is being blocked by spid -2. Also, when I
run sp_lock, there are several locks on the table I'm querying that have a
spid of -2. There are no errors in the errorlog.
There is no process with a spid = -2, so I don't have anything to kill. How
can I get rid of this without rebooting the SQL Server?>
> I'm running SQL 2K SP3, and I have a query that hangs and when I check
the
> sysprocesses table, the process is being blocked by spid -2. Also, when
I
> run sp_lock, there are several locks on the table I'm querying that have
a
> spid of -2. There are no errors in the errorlog.
> There is no process with a spid = -2, so I don't have anything to kill.
How
> can I get rid of this without rebooting the SQL Server?
--
From SQL Server Books Online:
In SQL Server 2000, all orphaned DTC transactions are assigned the SPID
value of '-2'. Orphaned DTC transactions are distributed transactions that
are not associated with any SPID. Thus, when an orphaned transaction is
blocking another process, this orphaned distributed transaction can be
identified by its distinctive '-2' SPID value. For more information, see
KILL.
UOW
Identifies the Unit of Work ID (UOW) of the DTC transaction. UOW is a
character string that may be obtained from the syslockinfo table, which
gives the UOW for every lock held by a DTC transaction. UOW also may be
obtained from the error log or through the DTC monitor. For more
information on monitoring distributed transactions, see the MS DTC user
manual.
Use KILL UOW to terminate orphaned DTC transactions, which are not
associated with any real SPID and instead are associated artificially with
SPID = '-2'. For more information on SPID = '-2', see the Remarks section
later in this topic.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.