Eliminating a table scan from a query

I'm doing a simple join from an inventory table to a product table to look up
the product name. Both the inventory table and product table have indexes on
the shared key. Nevertheless its using a table-scan against the product
table, which is taking up a lot of time (over 1/2 of the outer query this is
part of). Here it is:
SELECT position, value, a.productId, a.productname
FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
Looking at the output of Query Analyzer, it is using the index on positions
for the bank, then table scanning products.
Any ideas?
MauryABS(position) > 0.001
Don't have functions etc on the column side of the query. In most cases it will prohibit the
optimizer from using a column. Unless I misunderstand ABS, you should be able to write it as:
position > 0.001 OR position < -0.001
Above doesn't mean that the plan *will* change but at least the optimizer has a chance now to use an
index on that column. Whether or not that happens depends on a lot of factors, like type of join,
join density, number of rows in the tables, selectivity of each restriction, accuracy of statistics
etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:D2261870-06AC-429B-9870-5CC6ACAA14E2@.microsoft.com...
> I'm doing a simple join from an inventory table to a product table to look up
> the product name. Both the inventory table and product table have indexes on
> the shared key. Nevertheless its using a table-scan against the product
> table, which is taking up a lot of time (over 1/2 of the outer query this is
> part of). Here it is:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
> WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
> Looking at the output of Query Analyzer, it is using the index on positions
> for the bank, then table scanning products.
> Any ideas?
> Maury|||> SELECT position, value, a.productId, a.productname
> FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
> WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
> Looking at the output of Query Analyzer, it is using the index on positions
> for the bank, then table scanning products.
> Any ideas?
> Maury
Hi,
That is why it is taking such a long time. It should be doing an index
join on products. For some reason optimizer decided to chose the index
on position. It is very smart but not smart enough.|||"Tibor Karaszi" wrote:
> position > 0.001 OR position < -0.001
No luck, unfortunately.
Oh wow, I was just able to eliminate the scan! I have no idea why this
works, but when I added a criterion on the products by joining against
another table of product types, then it worked instantly.
Now here's something interesting... when I use ABS in _that_ query it
behaves as you mention above and the table scan comes back. Very odd!
Maury|||Ok, this is just too weird.
In order to try to give the optimizer a little more to work with, I added a
join against a product type table. This table has a column that can be used
to find all products of a particular type (pumbing, etc.), and has seven
unique values. Here is the new version of the query:
SELECT position, value, a.productId, a.productname
FROM tblPositions p
INNER JOIN tblproducts a ON p.productId= a.productId
INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
WHERE p.bank not in (300, 400, 1866)
AND (position > 0.01 OR position < -0.01)
Ok, ready for this? If the value on assetType is > 1 the table scan
disappears and the query runs instantly. If the value is >0 or >=1, the table
scan re-appears!
So is there some way to force the system to use the plan I want?
Maury|||It should not surprise you that you get different execution plans when you change the query, and
also when you change search criteria. This is what a cost-based optimizer is all about. Perhaps
there is a significant change in selectivity between > 0 and > 1, explaining why different execution
plans are chosen. Yes, there are several methods to force different aspects of a plan. For example
an index hint. I suggest you read about "optimizer hints" in Books Online for more information.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:1503F046-4007-4228-93D8-D6BA32D1DD7E@.microsoft.com...
> Ok, this is just too weird.
> In order to try to give the optimizer a little more to work with, I added a
> join against a product type table. This table has a column that can be used
> to find all products of a particular type (pumbing, etc.), and has seven
> unique values. Here is the new version of the query:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p
> INNER JOIN tblproducts a ON p.productId= a.productId
> INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
> WHERE p.bank not in (300, 400, 1866)
> AND (position > 0.01 OR position < -0.01)
> Ok, ready for this? If the value on assetType is > 1 the table scan
> disappears and the query runs instantly. If the value is >0 or >=1, the table
> scan re-appears!
> So is there some way to force the system to use the plan I want?
> Maury|||One more thing. Try rebuilding the indexes and updating statistics on the
tables to see if the optimizer can get better heuristics to work with. It
isn't that the optimizer isn't 'smart enough' as someone suggested. It is
indeed PLENTY smart - but it only has a limited set of data to work with and
that comes from the statistics information. It will usually switch from
index usage to table scan if it estimates more than roughly 10-15% (IIRC) of
the number of rows will match your join/where clauses.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1503F046-4007-4228-93D8-D6BA32D1DD7E@.microsoft.com...
> Ok, this is just too weird.
> In order to try to give the optimizer a little more to work with, I added
> a
> join against a product type table. This table has a column that can be
> used
> to find all products of a particular type (pumbing, etc.), and has seven
> unique values. Here is the new version of the query:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p
> INNER JOIN tblproducts a ON p.productId= a.productId
> INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
> WHERE p.bank not in (300, 400, 1866)
> AND (position > 0.01 OR position < -0.01)
> Ok, ready for this? If the value on assetType is > 1 the table scan
> disappears and the query runs instantly. If the value is >0 or >=1, the
> table
> scan re-appears!
> So is there some way to force the system to use the plan I want?
> Maury|||"TheSQLGuru" wrote:
> that comes from the statistics information. It will usually switch from
> index usage to table scan if it estimates more than roughly 10-15% (IIRC) of
> the number of rows will match your join/where clauses.
Ahhh, a useful number I'll try to remember.
Maury

Eliminating a table scan from a query

I'm doing a simple join from an inventory table to a product table to look u
p
the product name. Both the inventory table and product table have indexes on
the shared key. Nevertheless its using a table-scan against the product
table, which is taking up a lot of time (over 1/2 of the outer query this is
part of). Here it is:
SELECT position, value, a.productId, a.productname
FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
Looking at the output of Query Analyzer, it is using the index on positions
for the bank, then table scanning products.
Any ideas?
MauryABS(position) > 0.001
Don't have functions etc on the column side of the query. In most cases it w
ill prohibit the
optimizer from using a column. Unless I misunderstand ABS, you should be abl
e to write it as:
position > 0.001 OR position < -0.001
Above doesn't mean that the plan *will* change but at least the optimizer ha
s a chance now to use an
index on that column. Whether or not that happens depends on a lot of factor
s, like type of join,
join density, number of rows in the tables, selectivity of each restriction,
accuracy of statistics
etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in messag
e
news:D2261870-06AC-429B-9870-5CC6ACAA14E2@.microsoft.com...
> I'm doing a simple join from an inventory table to a product table to look
up
> the product name. Both the inventory table and product table have indexes
on
> the shared key. Nevertheless its using a table-scan against the product
> table, which is taking up a lot of time (over 1/2 of the outer query this
is
> part of). Here it is:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
> WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
> Looking at the output of Query Analyzer, it is using the index on position
s
> for the bank, then table scanning products.
> Any ideas?
> Maury|||> SELECT position, value, a.productId, a.productname
> FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
> WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
> Looking at the output of Query Analyzer, it is using the index on position
s
> for the bank, then table scanning products.
> Any ideas?
> Maury
Hi,
That is why it is taking such a long time. It should be doing an index
join on products. For some reason optimizer decided to chose the index
on position. It is very smart but not smart enough.|||"Tibor Karaszi" wrote:

> position > 0.001 OR position < -0.001
No luck, unfortunately.
Oh wow, I was just able to eliminate the scan! I have no idea why this
works, but when I added a criterion on the products by joining against
another table of product types, then it worked instantly.
Now here's something interesting... when I use ABS in _that_ query it
behaves as you mention above and the table scan comes back. Very odd!
Maury|||Ok, this is just too weird.
In order to try to give the optimizer a little more to work with, I added a
join against a product type table. This table has a column that can be used
to find all products of a particular type (pumbing, etc.), and has seven
unique values. Here is the new version of the query:
SELECT position, value, a.productId, a.productname
FROM tblPositions p
INNER JOIN tblproducts a ON p.productId= a.productId
INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
WHERE p.bank not in (300, 400, 1866)
AND (position > 0.01 OR position < -0.01)
Ok, ready for this? If the value on assetType is > 1 the table scan
disappears and the query runs instantly. If the value is >0 or >=1, the tabl
e
scan re-appears!
So is there some way to force the system to use the plan I want?
Maury|||It should not surprise you that you get different execution plans when you c
hange the query, and
also when you change search criteria. This is what a cost-based optimizer is
all about. Perhaps
there is a significant change in selectivity between > 0 and > 1, explaining
why different execution
plans are chosen. Yes, there are several methods to force different aspects
of a plan. For example
an index hint. I suggest you read about "optimizer hints" in Books Online fo
r more information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in messag
e
news:1503F046-4007-4228-93D8-D6BA32D1DD7E@.microsoft.com...
> Ok, this is just too weird.
> In order to try to give the optimizer a little more to work with, I added
a
> join against a product type table. This table has a column that can be use
d
> to find all products of a particular type (pumbing, etc.), and has seven
> unique values. Here is the new version of the query:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p
> INNER JOIN tblproducts a ON p.productId= a.productId
> INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
> WHERE p.bank not in (300, 400, 1866)
> AND (position > 0.01 OR position < -0.01)
> Ok, ready for this? If the value on assetType is > 1 the table scan
> disappears and the query runs instantly. If the value is >0 or >=1, the ta
ble
> scan re-appears!
> So is there some way to force the system to use the plan I want?
> Maury|||One more thing. Try rebuilding the indexes and updating statistics on the
tables to see if the optimizer can get better heuristics to work with. It
isn't that the optimizer isn't 'smart enough' as someone suggested. It is
indeed PLENTY smart - but it only has a limited set of data to work with and
that comes from the statistics information. It will usually switch from
index usage to table scan if it estimates more than roughly 10-15% (IIRC) of
the number of rows will match your join/where clauses.
TheSQLGuru
President
Indicium Resources, Inc.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1503F046-4007-4228-93D8-D6BA32D1DD7E@.microsoft.com...
> Ok, this is just too weird.
> In order to try to give the optimizer a little more to work with, I added
> a
> join against a product type table. This table has a column that can be
> used
> to find all products of a particular type (pumbing, etc.), and has seven
> unique values. Here is the new version of the query:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p
> INNER JOIN tblproducts a ON p.productId= a.productId
> INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
> WHERE p.bank not in (300, 400, 1866)
> AND (position > 0.01 OR position < -0.01)
> Ok, ready for this? If the value on assetType is > 1 the table scan
> disappears and the query runs instantly. If the value is >0 or >=1, the
> table
> scan re-appears!
> So is there some way to force the system to use the plan I want?
> Maury|||"TheSQLGuru" wrote:
> that comes from the statistics information. It will usually switch from
> index usage to table scan if it estimates more than roughly 10-15% (IIRC)
of
> the number of rows will match your join/where clauses.
Ahhh, a useful number I'll try to remember.
Maury

Eliminating (x row(s) affected) message

I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.Kevin wrote on Fri, 2 Jun 2006 07:39:02 -0700:

> I am sure this has been asked before. But, in searching for this message I
> get a number of returns.
> How do I eliminate the (x row(s) affected) message from queries in a
> stored procedure?
> Thank you.
Use SET NOCOUNT ON at the start of the stored proc.
Dan|||Try:
SET NOCOUNT ON
... at the beginning of each proc.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:741BD8D2-F958-471B-95FD-F694ACFDD0AD@.microsoft.com...
I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.

Eliminating (x row(s) affected) message

I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.Kevin wrote on Fri, 2 Jun 2006 07:39:02 -0700:
> I am sure this has been asked before. But, in searching for this message I
> get a number of returns.
> How do I eliminate the (x row(s) affected) message from queries in a
> stored procedure?
> Thank you.
Use SET NOCOUNT ON at the start of the stored proc.
Dan|||Try:
SET NOCOUNT ON
... at the beginning of each proc.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:741BD8D2-F958-471B-95FD-F694ACFDD0AD@.microsoft.com...
I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.

eliminating (inverse) duplicates in result

Am I going about this the right way? I want to find pairs of entities
in a table that have some relationship (such as a field being the
same), so I

select t1.id, t2.id from sametable t1 join sametable t2 on
t1.id<>t2.id
where t1.fieldx=t2.fieldx ...

The trouble is, this returns each pair twice, e.g.

B C
C B
M N
N M

Is there a way to do this kind of thing and only get each pair once?

KerryTry this:

SELECT t1.id, t2.id
FROM sametable t1
JOIN sametable t2
ON t1.id < t2.id
WHERE t1.fieldx=t2.fieldx ...

(a subtle difference in the ON clause)

--
David Portas
----
Please reply only to the newsgroup
--|||Many thanks for the tip. Very elegant. Makes the join half as big too!

Thanks,

Kerry

Eliminating "Nan" in reports

I'd like to eliminate "Nan" in my reports and I've tried various versions of
IIF, but keep getting an error message.
This is what I want/have:
= Sum(Fields!SubWin.Value, "WinRate1_All")+ Sum(Fields!PrimeWin.Value,
"WinRate1_All")/ Sum(Fields!SubWin.Value, "WinRate1_All")+
Sum(Fields!PrimeWin.Value, "WinRate1_All")+ Sum(Fields!SubLoss.Value,
"WinRate1_All")+ Sum(Fields!PrimeLoss.Value, "WinRate1_All")
Thanks for your help!You are probably dividing by zero. Try something like
=IIF(Sum(Fields!SubWin.Value, "WinRate1_All")+
Sum(Fields!PrimeWin.Value, "WinRate1_All")+ Sum(Fields!SubLoss.Value,
"WinRate1_All")+ Sum(Fields!PrimeLoss.Value, "WinRate1_All") <> 0,
Sum(Fields!SubWin.Value, "WinRate1_All")+ Sum(Fields!PrimeWin.Value,
"WinRate1_All")/ Sum(Fields!SubWin.Value, "WinRate1_All")+
Sum(Fields!PrimeWin.Value, "WinRate1_All")+ Sum(Fields!SubLoss.Value,
"WinRate1_All")+ Sum(Fields!PrimeLoss.Value, "WinRate1_All"),0)
This way you are only performing the division if the denominator is not
zero. If the denominator is zero, then return a zero.|||That worked perfectly!
Thank you so very much for your response!
"dba56" wrote:
> You are probably dividing by zero. Try something like
> =IIF(Sum(Fields!SubWin.Value, "WinRate1_All")+
> Sum(Fields!PrimeWin.Value, "WinRate1_All")+ Sum(Fields!SubLoss.Value,
> "WinRate1_All")+ Sum(Fields!PrimeLoss.Value, "WinRate1_All") <> 0,
> Sum(Fields!SubWin.Value, "WinRate1_All")+ Sum(Fields!PrimeWin.Value,
> "WinRate1_All")/ Sum(Fields!SubWin.Value, "WinRate1_All")+
> Sum(Fields!PrimeWin.Value, "WinRate1_All")+ Sum(Fields!SubLoss.Value,
> "WinRate1_All")+ Sum(Fields!PrimeLoss.Value, "WinRate1_All"),0)
> This way you are only performing the division if the denominator is not
> zero. If the denominator is zero, then return a zero.
>|||That was perfect!
Thank you very much for your response!
"dba56" wrote:
> You are probably dividing by zero. Try something like
> =IIF(Sum(Fields!SubWin.Value, "WinRate1_All")+
> Sum(Fields!PrimeWin.Value, "WinRate1_All")+ Sum(Fields!SubLoss.Value,
> "WinRate1_All")+ Sum(Fields!PrimeLoss.Value, "WinRate1_All") <> 0,
> Sum(Fields!SubWin.Value, "WinRate1_All")+ Sum(Fields!PrimeWin.Value,
> "WinRate1_All")/ Sum(Fields!SubWin.Value, "WinRate1_All")+
> Sum(Fields!PrimeWin.Value, "WinRate1_All")+ Sum(Fields!SubLoss.Value,
> "WinRate1_All")+ Sum(Fields!PrimeLoss.Value, "WinRate1_All"),0)
> This way you are only performing the division if the denominator is not
> zero. If the denominator is zero, then return a zero.
>

Eliminate top and bottom percent of records

I have a temp table that is being populated and below is a representation of the data.Basically, for each mile_code and rep combination there will be ‘X’ number of records.(In the example below there are 30 records.)What I need to do is eliminate the top and bottom 10% (This could be any percentage and I want to be able to change that easily if I have to.) of DeltaT for each mile_code and rep combination and then average the remaining values in DeltaT for each mile_code and rep combination.

I’ve tried a bunch of different approaches and none give me what I’m looking for.Any ideas or solutions?

mile_code DeltaT rep close_date

Approach 1 Coleman 6/5/2007 16:52

Approach 1 COLEMAN 6/5/2007 14:32

Approach 1 COLEMAN 6/5/2007 9:26

Approach 21 COLEMAN 5/31/2007 14:07

Approach 14 COLEMAN 5/31/2007 9:53

Approach 7 Coleman 5/30/2007 16:12

Approach 36 COLEMAN 5/29/2007 15:53

Approach 8 COLEMAN 5/29/2007 13:54

Approach 8 COLEMAN 5/29/2007 13:23

Approach 8 COLEMAN 5/29/2007 9:00

… (20 more records)

Interview 1 Coleman 6/5/2007 16:52

Interview 1 COLEMAN 6/5/2007 14:32

Interview 1 COLEMAN 6/5/2007 9:26

Interview 8 COLEMAN 5/29/2007 13:54

Interview 8 COLEMAN 5/29/2007 9:00

Interview 14 COLEMAN 5/25/2007 8:37

Interview 61 COLEMAN 5/24/2007 10:57

Interview 14 COLEMAN 5/23/2007 9:32

Interview 26 COLEMAN 5/18/2007 9:39

Interview 23 Coleman 5/14/2007 13:25

… (20 more records)

Followed by 30 each of the other 3 milecodes for Coleman

Approach 0 DOLAN 6/6/2007 9:27

Approach 1 DOLAN 6/5/2007 13:39

Approach 1 DOLAN 6/5/2007 12:20

Approach 1 DOLAN 6/5/2007 11:37

Approach 92 DOLAN 6/5/2007 11:12

Approach 91 DOLAN 6/5/2007 9:44

Approach 2 Dolan 6/4/2007 11:53

Approach 2 DOLAN 6/4/2007 11:11

Approach 5 Dolan 6/1/2007 16:07

Approach 177 DOLAN 6/1/2007 10:58

… (20 more records)

Interview 96 DOLAN 6/5/2007 16:38

Interview 167 DOLAN 6/5/2007 14:24

Interview 2 Dolan 6/4/2007 14:54

Interview 2 DOLAN 6/4/2007 11:11

Interview 5 Dolan 6/1/2007 16:07

Interview 27 DOLAN 6/1/2007 14:48

Interview 323 DOLAN 6/1/2007 13:49

Interview 15 Dolan 6/1/2007 11:48

Interview 224 DOLAN 5/31/2007 16:40

Interview 126 Dolan 5/30/2007 15:02

… (20 more records)

…

Here is one way.

Code Snippet

select *

from tb t1

where t1.deltat in (select top 90 percent t2.deltat from tb t2 where t2.mile_code=t1.mile_code order by t2.deltat asc)

and t1.deltat in (select top 90 percent t3.deltat from tb t3 where t3.mile_code=t1.mile_code order by t3.deltat desc)

|||

What should be the criteria to eliminate the top and bottom 10 % of [DeltaT], other than [mile_code] and [rep]?

Should we sort by [mile_code], [rep] and [DeltaT] and then eliminate the top and bottom 10%?

AMB

|||I thought of this on the way to work this morning and realized the way I described what I wanted was incorrect. If the data is sorted by DeltaT for each mile_code and rep combination then in this example (30 records for each group and 10% gone from top to bottom) the first 3 records and last 3 records should be removed. So it doesn't matter what the values of DeltaT are. If there were fractions caused by the number of records chosen and/or the percentage then I'd want it to just round to the nearest whole number.|||

Try:

Code Snippet

createtable #t (

mile_code varchar(25)notnull,

rep varchar(25)notnull,

deltat intnotnull

)

go

declare @.i int, @.j int

declare @.mile_code varchar(25)

declare @.rep varchar(25)

set @.j = 1

while @.j <= 2

begin

set @.rep =

case

when @.j = 1 then'COLEMAN'

when @.j = 2 then'DOLAN '

end

set @.i = 1

while @.i <= 60

begin

set @.mile_code =

case

when @.i between 1 and 30 then'Approach'

when @.i between 31 and 60 then'Interview'

end

insertinto #t values(@.mile_code, @.rep,(abs(checksum(newid()))% 100)+ 1)

set @.i = @.i + 1

end

set @.j = @.j + 1

end

go

declare @.percent int

set @.percent = 10

;with cte

as

(

select

mile_code,

rep,

deltat,

((row_number()over(partitionby mile_code, rep orderby deltat))* 100.00)/(count(*)over(partitionby mile_code, rep))as pct

from

#t

)

select

mile_code,

rep,

avg(deltat)as avg_deltat

from

cte

where

pct between @.percent and (100.00 - @.percent)

groupby

mile_code,

rep

orderby

mile_code,

rep

go

droptable #t

go

AMB

|||

I am not perfectly clear about what you are trying to do, but here is a solution that removes the top 10 percent using a CTE and a couple of aggregates. Note this will only work in 2005...

;with rowCounts as (

select row_number() over (partition by mile_code,rep order by closeDate) as rowNum, --gives a row number per group, ordered by close date
count(*) over (partition by mile_code,rep) as totalRows, --gets total rows by mile_code and rep. This is how I do the work later
mile_code,rep, deltaT, closeDate
from #t) --this is the same as his #t, plus the close date. I will repeat it after this explanation

select mile_code,rep, deltaT, closeDate, rowNum, totalRows
from rowCounts
where rowNum > totalRows * (@.percent / 100.0) --removes the bottom 10 percent
and rowNum < totalRows - (totalRows * (@.percent / 100.0)) -- removes the top

The whole code (thanks to hunchback):

set nocount on
create table #t (
mile_code varchar(25) not null,
rep varchar(25) not null,
deltat int not null,
closeDate datetime
)
go
declare @.i int, @.j int
declare @.mile_code varchar(25)
declare @.rep varchar(25)
declare @.closeDate datetime
set @.j = 1
while @.j <= 2
begin
set @.rep =
case
when @.j = 1 then 'COLEMAN'
when @.j = 2 then 'DOLAN '
end
set @.i = 1

while @.i <= 60
begin
set @.mile_code =
case
when @.i between 1 and 22 then 'Approach'
when @.i between 23 and 60 then 'Interview'
end
insert into #t values(@.mile_code, @.rep, (abs(checksum(newid())) % 100) + 1,
dateadd(day,(abs(checksum(newid())) % 100) + 1,'20070101'))
set @.i = @.i + 1
end
set @.j = @.j + 1
end

go

declare @.percent int
set @.percent = 10
;with rowCounts as (
select row_number() over (partition by mile_code,rep order by closeDate) as rowNum,
count(*) over (partition by mile_code,rep) as totalRows,
mile_code,rep, deltaT, closeDate
from #t)
select mile_code,rep, deltaT, closeDate, rowNum, totalRows
from rowCounts
where rowNum > totalRows * (@.percent / 100.0)
and rowNum < totalRows - (totalRows * (@.percent / 100.0))

drop table #t

go

|||here's another alternative,

again, thanks to hunchback for the test data

create table #t (
mile_code varchar(25) not null,
rep varchar(25) not null,
deltat int not null
)
go

declare @.i int, @.j int
declare @.mile_code varchar(25)
declare @.rep varchar(25)
set @.j = 1

while @.j <= 2
begin
set @.rep =
case
when @.j = 1 then 'COLEMAN'
when @.j = 2 then 'DOLAN '
end

set @.i = 1

while @.i <= 60
begin
set @.mile_code =
case
when @.i between 1 and 30 then 'Approach'
when @.i between 31 and 60 then 'Interview'
end

insert into #t values(@.mile_code, @.rep, (abs(checksum(newid())) % 100) + 1)
set @.i = @.i + 1
end

set @.j = @.j + 1
end
go

SELECT IDENTITY(int,1,1) AS row_number
, *
INTO #a
FROM #t
ORDER BY
mile_code
, rep
, deltat

DECLARE @.percnt int
SET @.percnt = 10

SELECT a.mile_code
, a.rep
, a.deltat
, row_number - minrow AS grp_row_number
, maxrow * (@.percnt/100.0) as countpergrp
FROM #a a INNER JOIN
(
SELECT mile_code
, rep
, MIN(row_number) - 1 AS minrow
, COUNT(*) AS maxrow
FROM #a
GROUP BY
mile_code
, rep
) b on a.mile_code = b.mile_code
and a.rep = b.rep
WHERE row_number - minrow between (maxrow * (@.percnt/100.0)) and (maxrow - (maxrow * (@.percnt/100.0)))

DROP TABLE #t
DROP TABLE #a
GO|||

Thanks everyone. Still have a little more work to do to get the final result before putting it into producton (Have to add some other calculations but will do that by joining in another temp table or something.) but all of your input helped a lot. Here is what I ended up with thanks to your help:

DECLARE @.percent int,

@.recentindex int

SET @.percent = 10

SET @.recentindex = 45

Then crate temp table #miledata to get the last 'x' number of records for each rep in each mile_code that they have completed. Do this based off of the @.recentindex variable.

Then use either of the following to get results. Haven't decided which I'll put into production yet.

OPTION A

/*This creates a common table expression to run our aggregate query against.*/

;WITH cte_AveRecords AS

(

SELECT row_number() OVER(partition BY mile_code, rep ORDER BY deltaT) AS rowNum, --gives a row number per group, ordered by deltaT

COUNT(*) OVER (partition BY mile_code, rep) AS totalRows, --gets total rows by mile_code and rep.

mile_code,

rep,

deltaT,

recentindex

FROM#miledata

)

/*This deterimines the average per mile_code and rep by selecting the rows that fall above the low percent and less than or equal to the upper percent.*/

SELECT c.mile_code,

c.rep,

CAST(AVG(CAST(a.deltaT AS Decimal(5,1))) AS Decimal(5,1)) AS AvgTime

FROM

(

SELECT mile_code, rep, deltaT, recentindex, rowNum, totalRows

FROM cte_AveRecords

WHERE rowNum > totalRows * (@.percent / 100.0) --removes the bottom 'x' percent

AND rowNum < totalRows - (totalRows * (@.percent / 100.0)) -- removes the top

)a

JOIN cte_AveRecords c ON a.rep = c.rep AND a.mile_code = c.mile_code

GROUP BY c.mile_code,

c.rep

ORDER BY c.rep,

c.mile_code

OPTION B

/*This creates a common table expression to run our aggregate query against.*/

;WITH cte_AveRecords AS

(

SELECT

mile_code,

rep,

deltat,

/*This assigns a percentage to each row number in the mile_code and rep group.See explaination A below*/

((row_number() over(partition by mile_code, rep order by deltat)) * 100.00) / (count(*) over(partition by mile_code, rep)) as pct

FROM #miledata

)

/*This deterimines the average per mile_code and rep based on what we populated the CTE with..*/

SELECT mile_code,

rep,

CAST(AVG(CAST(deltat AS Decimal(5,1))) AS Decimal(5,1)) AS avg_deltat

FROM cte_AveRecords

WHERE pct > @.percent and pct < (100.00 - @.percent)

GROUP BY mile_code,

rep

ORDER BY rep,

mile_code

GO