Sorting in Sql Server 2008 R2 Express

More importantly ... are you running Tough Mudder - Twin Cities this weekend?

Not running it...wish we were timing it. :)  Any leads?

i agree with your second contention.  I am asking for help writing a stored procedure that gets the points for each person out of one table (indresults) from several different races and then totals up those points and sorts them.  Quite honestly I am working on the sort using an ajax datatable so that is less of an issue but if I can pull the data and assign points to each series participant more quickly than I am currently doing, that would be my desire.

Yes that is what I need help with.  I'm sorry.  I forgot this function:

Private Function GetMyPts(lThisRaceID, lThisPartID, iMyAge)
    Dim iMyPl
    Dim bInRace
    Dim iNumFin

    iNumFin = 0
    GetMyPts = 0
    iMyPl = 0
    bINRace = False

    Set rs = Server.CreateObject("ADODB.Recordset")
    If CInt(iAgeTo) = 0 Then   'get open standings
        sql = "SELECT ir.ParticipantID FROM IndResults ir INNER JOIN Participant p ON ir.ParticipantID = p.ParticipantID WHERE ir.RaceID = " 
        sql = sql & lThisRaceID & " AND p.Gender = '" & sGender & "' AND ir.FnlTime IS NOT NULL AND ir.FnlTime <> '00:00:00.000' ORDER BY ir.EventPl" 
    Else
        sql = "SELECT ir.ParticipantID FROM IndResults ir INNER JOIN Participant p ON ir.ParticipantID = p.ParticipantID "
        sql = sql & "INNER JOIN PartRace pr ON ir.RaceID = pr.RaceID WHERE pr.RaceID = " & lThisRaceID & " AND ir.RaceID = " 
        sql = sql & lThisRaceID & " AND ir.FnlTime IS NOT NULL AND ir.FnlTime <> '00:00:00.000' AND p.Gender = '" & sGender & "' AND pr.Age >= " 
        sql = sql & iAgeFrom & " AND pr.Age <= " & iAgeTo & " ORDER BY ir.EventPl"
    End If

    rs.Open sql, conn, 1, 2
    If rs.RecordCount > 0 Then iNumFin = rs.RecordCount
    Do While Not rs.EOF
        iMyPl = CInt(iMyPl) + 1
        If CLng(lThisPartID) = CLng(rs(0).Value) Then 
            bInRace = True
            Exit Do
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing

    If CInt(iNumFin) > 0 Then 
        If bInRace = True Then GetMyPts = Round(((CInt(iNumFin) - CInt(iMyPl) + 1)/CInt(iNumFin))*100, 2)
    End If
End FUnction

Select allOpen in new window

Thanks for all of your patience with my ineptitude folks.  See if the attachment helps at all.
Series-Results-Data-Structure.docx

Ok so let's start with this.  If this is what you need let me know and I will get the same from the other tables.  If this is not exactly correct let me know.  I have never done this before so your patience is appreciated!  (I took PartName out)

CREATE TABLE [dbo].[SeriesParts](
	[SeriesID] [bigint] NOT NULL,
	[SeriesPartsID] [bigint] IDENTITY(1,1) NOT NULL,
	[ParticipantID] [bigint] NOT NULL,
	[Age] [int] NOT NULL,
	[Gender] [varchar](1) NOT NULL,
	[EventPl1] [int] NULL,
	[EventPl2] [int] NULL,
	[EventPl3] [int] NULL,
	[EventPl4] [int] NULL,
	[EventPl5] [int] NULL,
	[EventPl6] [int] NULL,
	[EventPl7] [int] NULL,
	[EventPl8] [int] NULL,
	[EventPl9] [int] NULL,
	[EventPl10] [int] NULL,
 CONSTRAINT [PK_SeriesParts] PRIMARY KEY CLUSTERED 

Select allOpen in new window

20	1	4461	name1	64	M	0	0	0	0	0	0	0	0	0	0
20	2	4462	name2	38	M	0	0	0	0	0	0	0	0	0	0
20	3	4463	name3	20	F	0	0	0	0	0	0	0	0	0	0
20	4	4464	name4	34	F	0	0	0	0	0	0	0	0	0	0
20	5	4465	name5	43	F	0	0	0	0	0	0	0	0	0	0

Select allOpen in new window

Lets see if I can get you what you need today:

Participant:

CREATE TABLE [dbo].[Participant](
	[ParticipantID] [bigint] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](25) NOT NULL,
	[LastName] [varchar](25) NOT NULL,
	[City] [varchar](25) NULL,
	[St] [varchar](2) NULL,
	[Phone] [varchar](12) NULL,
	[Email] [varchar](50) NULL,
	[DOB] [datetime] NULL,
	[Gender] [varchar](1) NULL,
	[SendInfo] [varchar](1) NULL,
	[CCLogInvite] [varchar](1) NULL,
	[SendRslts] [varchar](1) NULL,
	[Comments] [varchar](1000) NULL,
	[Country] [varchar](3) NULL,
	[Uploaded] [varchar](1) NULL,
	[Twitter] [varchar](50) NULL,
	[FBook] [varchar](50) NULL,
 CONSTRAINT [PK_Participant] PRIMARY KEY CLUSTERED 
(
	[ParticipantID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Select allOpen in new window

Data:
ParticipantID      FirstName      LastName      City      St      Phone      Email      DOB      Gender      SendInfo      CCLogInvite      SendRslts      Comments      Country      Uploaded      Twitter      FBook
33      fname1   lname1        White Bear Lake      MN      651-653-6527      dignan2934@aol.com      1962-03-15 00:00:00.000      M      y      n      y      NULL      NULL      NULL      NULL      NULL
34      fname2   lname2        Somerset      WI      715-247-5845      NULL      1967-03-15 00:00:00.000      F      y      n      y      NULL      NULL      NULL      NULL      NULL
35      fname3   lname3        New Richmond      WI      715-246-5305      NULL      1954-03-15 00:00:00.000      M      y      n      y      NULL      NULL      NULL      NULL      NULL
36      fname4   lname4      Cumberland      WI      715-822-8776      NULL      1948-03-15 00:00:00.000      M      y      n      y      NULL      NULL      NULL      NULL      NULL

SeriesParts:

CREATE TABLE [dbo].[SeriesParts](
	[SeriesID] [bigint] NOT NULL,
	[SeriesPartsID] [bigint] IDENTITY(1,1) NOT NULL,
	[ParticipantID] [bigint] NOT NULL,
	[PartName] [varchar](50) NOT NULL,
	[Age] [int] NOT NULL,
	[Gender] [varchar](1) NOT NULL,
	[EventPl1] [int] NULL,
	[EventPl2] [int] NULL,
	[EventPl3] [int] NULL,
	[EventPl4] [int] NULL,
	[EventPl5] [int] NULL,
	[EventPl6] [int] NULL,
	[EventPl7] [int] NULL,
	[EventPl8] [int] NULL,
	[EventPl9] [int] NULL,
	[EventPl10] [int] NULL,
 CONSTRAINT [PK_SeriesParts] PRIMARY KEY CLUSTERED 
(
	[SeriesPartsID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[SeriesParts]  WITH CHECK ADD  CONSTRAINT [FK_SeriesParts_Series] FOREIGN KEY([SeriesID])
REFERENCES [dbo].[Series] ([SeriesID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[SeriesParts] CHECK CONSTRAINT [FK_SeriesParts_Series]
GO

ALTER TABLE [dbo].[SeriesParts] ADD  CONSTRAINT [DF_SeriesParts_EventPl11]  DEFAULT ((0)) FOR [EventPl1]
GO

ALTER TABLE [dbo].[SeriesParts] ADD  CONSTRAINT [DF_SeriesParts_EventPl2]  DEFAULT ((0)) FOR [EventPl2]
GO

ALTER TABLE [dbo].[SeriesParts] ADD  CONSTRAINT [DF_SeriesParts_EventPl3]  DEFAULT ((0)) FOR [EventPl3]
GO

ALTER TABLE [dbo].[SeriesParts] ADD  CONSTRAINT [DF_SeriesParts_EventPl4]  DEFAULT ((0)) FOR [EventPl4]
GO

ALTER TABLE [dbo].[SeriesParts] ADD  CONSTRAINT [DF_SeriesParts_EventPl5]  DEFAULT ((0)) FOR [EventPl5]
GO

ALTER TABLE [dbo].[SeriesParts] ADD  CONSTRAINT [DF_SeriesParts_EventPl6]  DEFAULT ((0)) FOR [EventPl6]
GO

ALTER TABLE [dbo].[SeriesParts] ADD  CONSTRAINT [DF_SeriesParts_EventPl7]  DEFAULT ((0)) FOR [EventPl7]
GO

ALTER TABLE [dbo].[SeriesParts] ADD  CONSTRAINT [DF_SeriesParts_EventPl8]  DEFAULT ((0)) FOR [EventPl8]
GO

ALTER TABLE [dbo].[SeriesParts] ADD  CONSTRAINT [DF_SeriesParts_EventPl9]  DEFAULT ((0)) FOR [EventPl9]
GO

ALTER TABLE [dbo].[SeriesParts] ADD  CONSTRAINT [DF_SeriesParts_EventPl10]  DEFAULT ((0)) FOR [EventPl10]
GO

Select allOpen in new window

Data:
20      1      4461      Anderson, Gordon      64      M      0      0      0      0      0      0      0      0      0      0
20      2      4462      Fjestad, Todd      38      M      0      0      0      0      0      0      0      0      0      0
20      3      4463      Becker, Steph      20      F      0      0      0      0      0      0      0      0      0      0
20      4      4464      Chicoine, Michele      34      F      0      0      0      0      0      0      0      0      0      0

PartRace:

CREATE TABLE [dbo].[PartRace](
	[ParticipantID] [bigint] NOT NULL,
	[PartRaceID] [bigint] IDENTITY(1,1) NOT NULL,
	[Age] [int] NULL,
	[AgeGrp] [varchar](13) NULL,
	[Bib] [varchar](6) NULL,
	[Clyde] [varchar](1) NOT NULL,
	[RcWlk] [varchar](1) NOT NULL,
	[WhlChr] [varchar](1) NOT NULL,
	[IndDelay] [varchar](12) NOT NULL,
	[RaceID] [bigint] NOT NULL,
	[Trans1In] [varchar](12) NOT NULL,
	[Trans1Out] [varchar](12) NOT NULL,
	[Trans2In] [varchar](12) NOT NULL,
	[Trans2Out] [varchar](12) NOT NULL,
	[Locked] [varchar](1) NULL,
	[Wave] [int] NULL,
 CONSTRAINT [PK_PartRace] PRIMARY KEY CLUSTERED 
(
	[PartRaceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PartRace] ADD  CONSTRAINT [DF_PartRace_Clyde]  DEFAULT ('n') FOR [Clyde]
GO

ALTER TABLE [dbo].[PartRace] ADD  CONSTRAINT [DF_PartRace_RcWlk]  DEFAULT ('n') FOR [RcWlk]
GO

ALTER TABLE [dbo].[PartRace] ADD  CONSTRAINT [DF_PartRace_WhlChr]  DEFAULT ('n') FOR [WhlChr]
GO

ALTER TABLE [dbo].[PartRace] ADD  CONSTRAINT [DF_PartRace_IndDelay]  DEFAULT ((0)) FOR [IndDelay]
GO

ALTER TABLE [dbo].[PartRace] ADD  CONSTRAINT [DF_PartRace_Trans1In]  DEFAULT ('00:00:00.000') FOR [Trans1In]
GO

ALTER TABLE [dbo].[PartRace] ADD  CONSTRAINT [DF_PartRace_Trans1Out]  DEFAULT ('00:00:00.000') FOR [Trans1Out]
GO

ALTER TABLE [dbo].[PartRace] ADD  CONSTRAINT [DF_PartRace_Trans2In]  DEFAULT ('00:00:00.000') FOR [Trans2In]
GO

ALTER TABLE [dbo].[PartRace] ADD  CONSTRAINT [DF_PartRace_Trans2Out]  DEFAULT ('00:00:00.000') FOR [Trans2Out]
GO

Select allOpen in new window

Data:
33      1      41      40 - 49      1      n      n      n      00:00      16      00:00      00:00      00:00      00:00      n      0
34      2      36      30 - 39      322      n      n      n      00:00      16      00:00      00:00      00:00      00:00      n      0
35      3      49      40 - 49      332      n      n      n      00:00      16      00:00      00:00      00:00      00:00      n      0
36      4      55      50 - 59      333      n      n      n      00:00      16      00:00      00:00      00:00      00:00      n      0

IndResults:

CREATE TABLE [dbo].[IndResults](
	[IndRsltsID] [bigint] IDENTITY(1,1) NOT NULL,
	[ParticipantID] [bigint] NOT NULL,
	[EventPl] [int] NOT NULL,
	[ElpsdTime] [varchar](12) NULL,
	[FnlTime] [varchar](12) NULL,
	[RaceID] [bigint] NOT NULL,
	[Eligible] [varchar](1) NOT NULL,
	[MyHistID] [bigint] NULL,
	[ChipStart] [varchar](12) NULL,
	[ChipTime] [varchar](12) NULL,
 CONSTRAINT [PK_IndResults] PRIMARY KEY CLUSTERED 
(
	[IndRsltsID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[IndResults] ADD  CONSTRAINT [DF_IndResults_Eligible]  DEFAULT ('y') FOR [Eligible]
GO

ALTER TABLE [dbo].[IndResults] ADD  CONSTRAINT [DF_IndResults_ChipStart]  DEFAULT ('00:00:00.000') FOR [ChipStart]
GO

ALTER TABLE [dbo].[IndResults] ADD  CONSTRAINT [DF_IndResults_ChipTime]  DEFAULT ('00:00:00.000') FOR [ChipTime]
GO

Select allOpen in new window

Data:
11      901      6      19:26      19:26      22      y      NULL      00:00:00.000      00:00:00.000
12      2829      7      19:36      19:36      22      y      NULL      00:00:00.000      00:00:00.000
14      531      9      19:39      19:39      22      y      NULL      00:00:00.000      00:00:00.000
15      2839      10      20:18      20:18      22      y      NULL      00:00:00.000      00:00:00.000

Sorry.  Here you go (I haven't lost interest...need this badly...just crazy busy with things)

Series:

CREATE TABLE [dbo].[Series](
	[SeriesID] [bigint] IDENTITY(1,1) NOT NULL,
	[SeriesName] [varchar](50) NOT NULL,
	[SeriesYear] [int] NOT NULL,
	[MinParticip] [int] NOT NULL,
	[PartAwds] [varchar](1) NULL,
	[PerfAwds] [varchar](1) NULL,
	[LastRsltsUpdate] [datetime] NULL,
 CONSTRAINT [PK_Series] PRIMARY KEY CLUSTERED 
(
	[SeriesID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Select allOpen in new window

A few rows:
ID     SeriesName                           SeriesYear MinPartic PartAwds PerfAwds LastUpdate
 6      Central Minnesota Series      2014              2             n               n              NULL
7      Northland Series                      2014               2             n               n              NULL

I will be out most of the day today but will have most of the rest of the week to sit down and respond.  I really appreciate this!!!

BobbaBuoy,

Hi, are we still working on this?

Yes please.  I am sorry.  I thought I was waiting for you.  I will have some time today and tomorrow...most of the week actually.  I would really appreciate if we could get this taken care of.

Thanks so much!

Just to be clear...ready whenever you are.

The needed parameters you refer to are via form submission (currently turning into an ajax datatable).  The "meets" are actually "races" in "events".  That looks to be missing from above so here it is:

CREATE TABLE [dbo].[Events](
	[EventDirID] [bigint] NOT NULL,
	[EventID] [bigint] IDENTITY(1,1) NOT NULL,
	[EventName] [varchar](50) NOT NULL,
	[EventDate] [datetime] NOT NULL,
	[Location] [varchar](50) NULL,
	[EventType] [int] NOT NULL,
	[Club] [varchar](50) NULL,
	[WebSite] [varchar](500) NULL,
	[Weather] [varchar](250) NULL,
	[Comments] [varchar](5000) NULL,
	[DateReg] [datetime] NOT NULL,
	[WhichService] [varchar](12) NOT NULL,
	[OnlineReg] [varchar](1) NOT NULL,
	[ShowOnline] [varchar](1) NOT NULL,
	[WhenShutdown] [datetime] NULL,
	[Edition] [int] NOT NULL,
	[EventGrp] [bigint] NOT NULL,
	[FeeIncrDate] [datetime] NULL,
	[OrderBy] [varchar](5) NOT NULL,
	[MgmtPlan] [varchar](15) NOT NULL,
	[NeedBibs] [varchar](1) NULL,
	[NeedPins] [varchar](1) NULL,
	[GalleryLink] [varchar](1000) NULL,
	[Invoice] [decimal](18, 2) NULL,
	[Status] [varchar](10) NULL,
	[Deposit] [decimal](18, 2) NULL,
	[TimingMethod] [varchar](12) NULL,
	[Proofs] [varchar](50) NULL,
	[EmbedLink] [varchar](2000) NULL,
	[EventFamilyID] [bigint] NOT NULL,
	[EventClass] [varchar](1) NULL,
	[AdminNotes] [varchar](2000) NULL,
	[FoundBy] [varchar](50) NULL,
	[Logo] [varchar](50) NULL,
	[OptOut] [varchar](1) NULL,
 CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED 
(
	[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Events] ADD  CONSTRAINT [DF_Events_WhichService]  DEFAULT ('race_mgmt') FOR [WhichService]
GO

ALTER TABLE [dbo].[Events] ADD  CONSTRAINT [DF_Events_OnlineReg]  DEFAULT ('y') FOR [OnlineReg]
GO

ALTER TABLE [dbo].[Events] ADD  CONSTRAINT [DF_Events_Edition]  DEFAULT ((0)) FOR [Edition]
GO

ALTER TABLE [dbo].[Events] ADD  CONSTRAINT [DF_Events_EventGrp]  DEFAULT ((0)) FOR [EventGrp]
GO

ALTER TABLE [dbo].[Events] ADD  CONSTRAINT [DF_Events_OrderBy]  DEFAULT ('Place') FOR [OrderBy]
GO

ALTER TABLE [dbo].[Events] ADD  CONSTRAINT [DF_Events_MgmtPlan]  DEFAULT ('Standard') FOR [MgmtPlan]
GO

ALTER TABLE [dbo].[Events] ADD  CONSTRAINT [DF_Events_NeedBibs]  DEFAULT ('n') FOR [NeedBibs]
GO

ALTER TABLE [dbo].[Events] ADD  CONSTRAINT [DF_Events_NeedPins]  DEFAULT ('n') FOR [NeedPins]
GO

ALTER TABLE [dbo].[Events] ADD  CONSTRAINT [DF_Events_Invoice]  DEFAULT ((0.00)) FOR [Invoice]
GO

ALTER TABLE [dbo].[Events] ADD  CONSTRAINT [DF_Events_Deposit]  DEFAULT ((0.00)) FOR [Deposit]
GO

ALTER TABLE [dbo].[Events] ADD  CONSTRAINT [DF_Events_TimingMethod]  DEFAULT ('RFID') FOR [TimingMethod]
GO

ALTER TABLE [dbo].[Events] ADD  CONSTRAINT [DF_Events_EventFamilyID]  DEFAULT ((0)) FOR [EventFamilyID]
GO

ALTER TABLE [dbo].[Events]  WITH CHECK ADD  CONSTRAINT [FK_Events_EventDir] FOREIGN KEY([EventDirID])
REFERENCES [dbo].[EventDir] ([EventDirID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

Select allOpen in new window

CREATE TABLE [dbo].[RaceData](
	[RaceID] [bigint] IDENTITY(1,1) NOT NULL,
	[EventID] [bigint] NOT NULL,
	[RaceName] [varchar](35) NOT NULL,
	[Dist] [varchar](10) NOT NULL,
	[Type] [int] NOT NULL,
	[EntryFeePre] [decimal](18, 2) NOT NULL,
	[EntryFee] [decimal](18, 2) NOT NULL,
	[StartTime] [varchar](12) NOT NULL,
	[RaceDelay] [varchar](12) NOT NULL,
	[Certified] [varchar](1) NOT NULL,
	[HasWhlchr] [varchar](1) NOT NULL,
	[HasClyde] [varchar](1) NOT NULL,
	[HasRcWlk] [varchar](1) NOT NULL,
	[StartType] [varchar](8) NOT NULL,
	[MAwds] [int] NOT NULL,
	[FAwds] [int] NOT NULL,
	[BibsFrom] [int] NOT NULL,
	[BibsTo] [int] NULL,
	[Rcd_Qual] [varchar](1) NOT NULL,
	[Descr] [varchar](50) NULL,
	[SortRsltsBy] [varchar](12) NOT NULL,
	[OnlineRegLink] [varchar](500) NULL,
	[AllowDuplAwds] [varchar](1) NOT NULL,
	[ChipStart] [varchar](1) NULL,
	[StartRdr] [int] NULL,
	[InSeries] [varchar](1) NOT NULL,
 CONSTRAINT [PK_RaceData] PRIMARY KEY CLUSTERED 
(
	[RaceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_EntryFeePre]  DEFAULT ((0)) FOR [EntryFeePre]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_EntryFee]  DEFAULT ((0)) FOR [EntryFee]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_RaceDelay]  DEFAULT ((0)) FOR [RaceDelay]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_Certified]  DEFAULT ('n') FOR [Certified]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_HasWhlchr]  DEFAULT ('n') FOR [HasWhlchr]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_HasClyde]  DEFAULT ('n') FOR [HasClyde]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_HasRcWlk]  DEFAULT ('n') FOR [HasRcWlk]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_StartType]  DEFAULT ('mass') FOR [StartType]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_MAwds]  DEFAULT ((0)) FOR [MAwds]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_FAwds]  DEFAULT ((0)) FOR [FAwds]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_BibsFrom]  DEFAULT ((0)) FOR [BibsFrom]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_BibsTo]  DEFAULT ((0)) FOR [BibsTo]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_Rcd_Qual]  DEFAULT ('n') FOR [Rcd_Qual]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_SortRsltsBy]  DEFAULT ('EventPl') FOR [SortRsltsBy]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_AllowDuplAwds]  DEFAULT ('n') FOR [AllowDuplAwds]
GO

ALTER TABLE [dbo].[RaceData] ADD  CONSTRAINT [DF_RaceData_InSeries]  DEFAULT ('n') FOR [InSeries]
GO

ALTER TABLE [dbo].[RaceData]  WITH CHECK ADD  CONSTRAINT [FK_RaceData_Events] FOREIGN KEY([EventID])
REFERENCES [dbo].[Events] ([EventID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[RaceData] CHECK CONSTRAINT [FK_RaceData_Events]
GO

Select allOpen in new window

Didn't help this one...I am sure it is the result of poor db design on my part, so I have restructured it.  Here is the new approach:

j = 0
ReDim Races(0)
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT sr.RaceID FROM SeriesRaces sr INNER JOIN SeriesEvents se ON sr.SeriesEventsID = se.SeriesEventsID "
sql = sql & "WHERE se.SeriesID = " & lSeriesID & " AND se.EventDate < '" & Date & "' ORDER BY se.EventDate"
rs.Open sql, conn, 1, 2
Do While Not rs.EOF
    Races(j) = rs(0).Value
    j = j + 1
    ReDim Preserve Races(j)
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing

iArrDim1 = UBound(Races) + 3

i = 0
ReDim Results(iArrDim1, 0)
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT ParticipantID, PartName, Age FROM SeriesParts WHERE SeriesID = " & lSeriesID & " AND Gender = '" & sGender & "' ORDER BY PartName"
rs.Open sql, conn, 1, 2
Do While Not rs.EOF
    Results(0, i) = rs(0).Value
    Results(1, i) = Replace(rs(1).Value, "''", "'")
    Results(2, i) = rs(2).Value
    Results(3, i) = "0"
    i = i + 1
    ReDim Preserve Results(iArrDim1, i)
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing

For i = 0 To UBound(Results, 2) - 1
    For j = 4 To iArrDim1
        Results(j, i) = GetMyPts(Races(j - 4), Results(0, i))
        Results(3, i) = CSng(Results(3, i)) + CSng(Results(j, i))
    Next
Next

'sort the array
For i = 0 To UBound(Results, 2) - 2
    For j = i + 1 To UBound(Results, 2) - 1
       If CSng(Results(3, i)) < CSng(Results(3, j)) Then
            For k = 0 To iArrDim1
                SortArr(k) = Results(k, i)
                Results(k, i) = Results(k, j)
                Results(k, j) = SortArr(k)
            Next
        End If
    Next
Next

For i = 0 To UBound(Results, 2) - 1
    Results(0, i) = i + 1
Next

Private Function GetMyPts(lThisRaceID, lThisPartID)
    GetMyPts = "0"

    Set rs = Server.CreateObject("ADODB.Recordset")
    If CInt(iAgeTo) = 0 Then   'get open standings
        sql = "SELECT st.GndrPts FROM SeriesStdgs st INNER JOIN SeriesParts sp ON st.SeriesPartsID = sp.SeriesPartsID WHERE st.RaceID = " & lThisRaceID 
        sql = sql & " AND sp.ParticipantID = " & lThisPartID
    Else
        sql = "SELECT st.GndrPts FROM SeriesStdgs st INNER JOIN SeriesParts sp ON st.SeriesPartsID = sp.SeriesPartsID WHERE st.RaceID = " & lThisRaceID 
        sql = sql & " AND sp.ParticipantID = " & lThisPartID
    End If
    rs.Open sql, conn, 1, 2
    If rs.RecordCount > 0 Then GetMyPts = rs(0).Value
    rs.Close
    Set rs = Nothing
End Function

Select allOpen in new window

I would have thought this would have been a ton faster but it seems just as slow as the other one.  It now only has to find the races in a series, get the participants in that series, and pull their scores from a table (either by gender or age), then sort it.  I would have thought this would have been much faster, no?  I haven't put a timer on it yet.

I've requested that this question be closed as follows:

Accepted answer: 32 points for Jim Horn's comment #a40204799
Assisted answer: 32 points for Jim Horn's comment #a40204847
Assisted answer: 32 points for PortletPaul's comment #a40208146
Assisted answer: 0 points for BobbaBuoy's comment #a40209634
Assisted answer: 32 points for PortletPaul's comment #a40210164
Assisted answer: 31 points for Scott Fell (padas)'s comment #a40212895
Assisted answer: 31 points for PortletPaul's comment #a40213060
Assisted answer: 31 points for Scott Fell (padas)'s comment #a40214831
Assisted answer: 31 points for PortletPaul's comment #a40215598
Assisted answer: 31 points for PortletPaul's comment #a40218295
Assisted answer: 31 points for PortletPaul's comment #a40226460
Assisted answer: 31 points for PortletPaul's comment #a40240967
Assisted answer: 31 points for PortletPaul's comment #a40242705
Assisted answer: 31 points for PortletPaul's comment #a40260617
Assisted answer: 31 points for PortletPaul's comment #a40261977
Assisted answer: 31 points for PortletPaul's comment #a40262175
Assisted answer: 31 points for PortletPaul's comment #a40262429

for the following reason:

You guys have been great.  I believe we are trying to work around poor db design.  I am going to try one more thing and then at least try to find a better sorting mechanism.

Thanks for all your help.

instead of points I would have preferred more feedback. Was http:#a40262429 anywhere close to what you need?

Sorry this appears to have dead-ended but I do have what i want now.  The help here was indispensable.  We are battling a poorly designed database that I initially created 12 years ago.  I modified my series db structure to update the series manually with the click of a button.  This puts more of the data in the database and allows me to use the sorting capabilities of sql server to do what I need to do.

Here is a page that would time out while loading but now loads pretty quickly: http://www.gopherstateevents.com/series/series_results.asp?series_id=4

Still have to finish passing parameters via ajax (the learning continues) but it is very close.

Thanks again for the copious amounts of time and knowledge you folks put in to this!

Thanks BobbaBuoy, that page is lightning in comparison the older one, well done.