email being sent, but no message

I have setup an email notifications system, that basically takes each
row from a table and sents out an email according to the data in that
row. The emails get sent, with the subject being filled as expected.
Only problem is that sometimes there is no message.

Here is the stored procedure that is being called every hour to send
the emails:

CREATE PROCEDURE dbo.RemindersSendEmails AS

--Cursor
DECLARE RemindersCursor CURSOR FOR
SELECT *
FROM RemindersTodaysAndUnsent

--Values for cursor
DECLARE
@.I_Reminder_ID bigint,
@.I_Notice_ID bigint,
@.V_Reminder_Text varchar(250),
@.SDT_Reminder_Date smalldatetime,
@.V_Email varchar(50),
@.I_Reminder_Type bigint,
@.SDT_Reminder_Sent smalldatetime,
@.I_Attempts_Made int,
@.V_Notice_Type varchar(50),
@.I_Notice_Period int,
@.V_Period_Description varchar(50),
@.I_Project_ID bigint,
@.V_Notice_Ref varchar(10)

--values for sending the mail
DECLARE @.NEWLINE varchar(2)

OPEN RemindersCursor

FETCH NEXT FROM RemindersCursor
INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
@.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
@.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
@.I_Notice_Period, @.V_Period_Description,
@.I_Project_ID, @.V_Notice_Ref
--INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
@.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
--@.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
@.I_Notice_Period, @.V_Period_Description,
--@.I_Project_ID, @.V_Notice_Ref

SET @.NEWLINE = char(10)

--PRINT 'start'

WHILE @.@.FETCH_STATUS = 0
BEGIN

DECLARE @.EmailMessage varchar(6000), @.Subject varchar(100), @.Status
int

SET @.Subject = RTRIM(CONVERT(varchar(8), @.I_Reminder_ID)) + ' Notice
Alert - Project ' + RTRIM(CONVERT(varchar(8), @.I_Project_ID)) + '
Notice Ref ' + RTRIM(@.V_Notice_Ref)

SET @.EmailMessage = 'Project: ' + RTRIM(CONVERT(varchar(8),
@.I_Project_ID)) + @.NEWLINE +
'Notice: ' + RTRIM(@.V_Notice_Ref) + @.NEWLINE +
'Notice Type: ' + RTRIM(@.V_Notice_Type) + ' - ' +
RTRIM(@.V_Period_Description) + @.NEWLINE +
'Reminder: ' + RTRIM(@.V_Reminder_Text) + @.NEWLINE + @.NEWLINE +
'Reminder date: ' + CONVERT(varchar(11), @.SDT_Reminder_Date) +
@.NEWLINE +
'Reminder sent: ' + CONVERT(varchar(11), GETDATE()) + @.NEWLINE +
'Email sent to: ' + @.V_Email + @.NEWLINE +
'Number of attempts made at sending this email (once every hour): '
+ CONVERT(varchar(4), @.I_Attempts_Made)
--@.I_Reminder_ID, @.I_Notice_ID, @.V_Email, @.I_Reminder_Type,
@.I_Notice_Period,

PRINT 'subject = ' + @.Subject
PRINT 'message = ' + @.EmailMessage

SET @.V_Email = LTRIM(RTRIM(@.V_Email))

EXEC @.Status = master..xp_sendmail @.recipients = @.V_Email,
@.message = @.EmailMessage,
@.subject = @.Subject

--PRINT 'XXXXXXXXXXXXXXXXXXXXXX status = ' + CONVERT(varchar(2),
@.Status)

--If send mail is a success
IF (@.Status = 0)
BEGIN
UPDATE Reminders
SET SDT_Reminder_Sent = GETDATE(), I_Attempts_Made =
@.I_Attempts_Made + 1
WHERE I_Reminder_ID = @.I_Reminder_ID
END
--Else send mail failed
ELSE
BEGIN
UPDATE Reminders
SET I_Attempts_Made = @.I_Attempts_Made + 1
WHERE I_Reminder_ID = @.I_Reminder_ID
END

-- Get the next reminder
FETCH NEXT FROM RemindersCursor
INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
@.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
@.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
@.I_Notice_Period, @.V_Period_Description,
@.I_Project_ID, @.V_Notice_Ref
END

--PRINT 'End'

CLOSE RemindersCursor
DEALLOCATE RemindersCursor
GOJust a guess-- I'd check to see if any of your variables are NULL the SET
statement that builds your @.EmailMessage variable. In that case maybe your
entire @.EmailMessage variable is getting set to NULL?

"Jagdip Singh Ajimal" <jsa1981@.hotmail.com> wrote in message
news:c84eb1b0.0411290218.6a8a5eb1@.posting.google.c om...
> I have setup an email notifications system, that basically takes each
> row from a table and sents out an email according to the data in that
> row. The emails get sent, with the subject being filled as expected.
> Only problem is that sometimes there is no message.
> Here is the stored procedure that is being called every hour to send
> the emails:
> CREATE PROCEDURE dbo.RemindersSendEmails AS
> --Cursor
> DECLARE RemindersCursor CURSOR FOR
> SELECT *
> FROM RemindersTodaysAndUnsent
> --Values for cursor
> DECLARE
> @.I_Reminder_ID bigint,
> @.I_Notice_ID bigint,
> @.V_Reminder_Text varchar(250),
> @.SDT_Reminder_Date smalldatetime,
> @.V_Email varchar(50),
> @.I_Reminder_Type bigint,
> @.SDT_Reminder_Sent smalldatetime,
> @.I_Attempts_Made int,
> @.V_Notice_Type varchar(50),
> @.I_Notice_Period int,
> @.V_Period_Description varchar(50),
> @.I_Project_ID bigint,
> @.V_Notice_Ref varchar(10)
> --values for sending the mail
> DECLARE @.NEWLINE varchar(2)
> OPEN RemindersCursor
> FETCH NEXT FROM RemindersCursor
> INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> @.I_Notice_Period, @.V_Period_Description,
> @.I_Project_ID, @.V_Notice_Ref
> --INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> -- @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> @.I_Notice_Period, @.V_Period_Description,
> -- @.I_Project_ID, @.V_Notice_Ref
> SET @.NEWLINE = char(10)
> --PRINT 'start'
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DECLARE @.EmailMessage varchar(6000), @.Subject varchar(100), @.Status
> int
> SET @.Subject = RTRIM(CONVERT(varchar(8), @.I_Reminder_ID)) + ' Notice
> Alert - Project ' + RTRIM(CONVERT(varchar(8), @.I_Project_ID)) + '
> Notice Ref ' + RTRIM(@.V_Notice_Ref)
> SET @.EmailMessage = 'Project: ' + RTRIM(CONVERT(varchar(8),
> @.I_Project_ID)) + @.NEWLINE +
> 'Notice: ' + RTRIM(@.V_Notice_Ref) + @.NEWLINE +
> 'Notice Type: ' + RTRIM(@.V_Notice_Type) + ' - ' +
> RTRIM(@.V_Period_Description) + @.NEWLINE +
> 'Reminder: ' + RTRIM(@.V_Reminder_Text) + @.NEWLINE + @.NEWLINE +
> 'Reminder date: ' + CONVERT(varchar(11), @.SDT_Reminder_Date) +
> @.NEWLINE +
> 'Reminder sent: ' + CONVERT(varchar(11), GETDATE()) + @.NEWLINE +
> 'Email sent to: ' + @.V_Email + @.NEWLINE +
> 'Number of attempts made at sending this email (once every hour): '
> + CONVERT(varchar(4), @.I_Attempts_Made)
> --@.I_Reminder_ID, @.I_Notice_ID, @.V_Email, @.I_Reminder_Type,
> @.I_Notice_Period,
> PRINT 'subject = ' + @.Subject
> PRINT 'message = ' + @.EmailMessage
> SET @.V_Email = LTRIM(RTRIM(@.V_Email))
> EXEC @.Status = master..xp_sendmail @.recipients = @.V_Email,
> @.message = @.EmailMessage,
> @.subject = @.Subject
> --PRINT 'XXXXXXXXXXXXXXXXXXXXXX status = ' + CONVERT(varchar(2),
> @.Status)
> --If send mail is a success
> IF (@.Status = 0)
> BEGIN
> UPDATE Reminders
> SET SDT_Reminder_Sent = GETDATE(), I_Attempts_Made =
> @.I_Attempts_Made + 1
> WHERE I_Reminder_ID = @.I_Reminder_ID
> END
> --Else send mail failed
> ELSE
> BEGIN
> UPDATE Reminders
> SET I_Attempts_Made = @.I_Attempts_Made + 1
> WHERE I_Reminder_ID = @.I_Reminder_ID
> END
>
> -- Get the next reminder
> FETCH NEXT FROM RemindersCursor
> INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> @.I_Notice_Period, @.V_Period_Description,
> @.I_Project_ID, @.V_Notice_Ref
> END
> --PRINT 'End'
> CLOSE RemindersCursor
> DEALLOCATE RemindersCursor
> GO|||"MissLivvy" <XeveryidiwantistakenX@.yahoo.com> wrote in message
news:jeRqd.3625$u81.3215@.newsread3.news.pas.earthl ink.net...
> Just a guess-- I'd check to see if any of your variables are NULL the SET
> statement that builds your @.EmailMessage variable. In that case maybe your
> entire @.EmailMessage variable is getting set to NULL?

Ah good one.

This got me once. I forgot abuot it.

> "Jagdip Singh Ajimal" <jsa1981@.hotmail.com> wrote in message
> news:c84eb1b0.0411290218.6a8a5eb1@.posting.google.c om...
> > I have setup an email notifications system, that basically takes each
> > row from a table and sents out an email according to the data in that
> > row. The emails get sent, with the subject being filled as expected.
> > Only problem is that sometimes there is no message.
> > Here is the stored procedure that is being called every hour to send
> > the emails:
> > CREATE PROCEDURE dbo.RemindersSendEmails AS
> > --Cursor
> > DECLARE RemindersCursor CURSOR FOR
> > SELECT *
> > FROM RemindersTodaysAndUnsent
> > --Values for cursor
> > DECLARE
> > @.I_Reminder_ID bigint,
> > @.I_Notice_ID bigint,
> > @.V_Reminder_Text varchar(250),
> > @.SDT_Reminder_Date smalldatetime,
> > @.V_Email varchar(50),
> > @.I_Reminder_Type bigint,
> > @.SDT_Reminder_Sent smalldatetime,
> > @.I_Attempts_Made int,
> > @.V_Notice_Type varchar(50),
> > @.I_Notice_Period int,
> > @.V_Period_Description varchar(50),
> > @.I_Project_ID bigint,
> > @.V_Notice_Ref varchar(10)
> > --values for sending the mail
> > DECLARE @.NEWLINE varchar(2)
> > OPEN RemindersCursor
> > FETCH NEXT FROM RemindersCursor
> > INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> > @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> > @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> > @.I_Notice_Period, @.V_Period_Description,
> > @.I_Project_ID, @.V_Notice_Ref
> > --INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> > @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> > -- @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> > @.I_Notice_Period, @.V_Period_Description,
> > -- @.I_Project_ID, @.V_Notice_Ref
> > SET @.NEWLINE = char(10)
> > --PRINT 'start'
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > DECLARE @.EmailMessage varchar(6000), @.Subject varchar(100), @.Status
> > int
> > SET @.Subject = RTRIM(CONVERT(varchar(8), @.I_Reminder_ID)) + ' Notice
> > Alert - Project ' + RTRIM(CONVERT(varchar(8), @.I_Project_ID)) + '
> > Notice Ref ' + RTRIM(@.V_Notice_Ref)
> > SET @.EmailMessage = 'Project: ' + RTRIM(CONVERT(varchar(8),
> > @.I_Project_ID)) + @.NEWLINE +
> > 'Notice: ' + RTRIM(@.V_Notice_Ref) + @.NEWLINE +
> > 'Notice Type: ' + RTRIM(@.V_Notice_Type) + ' - ' +
> > RTRIM(@.V_Period_Description) + @.NEWLINE +
> > 'Reminder: ' + RTRIM(@.V_Reminder_Text) + @.NEWLINE + @.NEWLINE +
> > 'Reminder date: ' + CONVERT(varchar(11), @.SDT_Reminder_Date) +
> > @.NEWLINE +
> > 'Reminder sent: ' + CONVERT(varchar(11), GETDATE()) + @.NEWLINE +
> > 'Email sent to: ' + @.V_Email + @.NEWLINE +
> > 'Number of attempts made at sending this email (once every hour): '
> > + CONVERT(varchar(4), @.I_Attempts_Made)
> > --@.I_Reminder_ID, @.I_Notice_ID, @.V_Email, @.I_Reminder_Type,
> > @.I_Notice_Period,
> > PRINT 'subject = ' + @.Subject
> > PRINT 'message = ' + @.EmailMessage
> > SET @.V_Email = LTRIM(RTRIM(@.V_Email))
> > EXEC @.Status = master..xp_sendmail @.recipients = @.V_Email,
> > @.message = @.EmailMessage,
> > @.subject = @.Subject
> > --PRINT 'XXXXXXXXXXXXXXXXXXXXXX status = ' + CONVERT(varchar(2),
> > @.Status)
> > --If send mail is a success
> > IF (@.Status = 0)
> > BEGIN
> > UPDATE Reminders
> > SET SDT_Reminder_Sent = GETDATE(), I_Attempts_Made =
> > @.I_Attempts_Made + 1
> > WHERE I_Reminder_ID = @.I_Reminder_ID
> > END
> > --Else send mail failed
> > ELSE
> > BEGIN
> > UPDATE Reminders
> > SET I_Attempts_Made = @.I_Attempts_Made + 1
> > WHERE I_Reminder_ID = @.I_Reminder_ID
> > END
> > -- Get the next reminder
> > FETCH NEXT FROM RemindersCursor
> > INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> > @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> > @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> > @.I_Notice_Period, @.V_Period_Description,
> > @.I_Project_ID, @.V_Notice_Ref
> > END
> > --PRINT 'End'
> > CLOSE RemindersCursor
> > DEALLOCATE RemindersCursor
> > GO

Email Alert

I'm trying to develop a email alert feature on my project. I was trying to approach with SQL trigger wich I think is the best option. Basically, when a new record is inserted into ad table, a email alert goes to peolpe who selected to receive alert wehen certain conditions are met. What would be the best approach? any examples?
ThanksThat sounds a lot likeSQL Server Notification Services, a free download if you are using SQL Server.
|||Thanks for the reply. Is MS Notification Services SP1 what I need?

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