Solved

Sorting in Sql Server 2008 R2 Express

Posted on 2014-07-18
34
235 Views
Last Modified: 2014-08-26
I am compiling standings in a running series.  A series can have up to 10 races although most have much fewer.  I am having difficulty with the sort.  I am currently writing the data to an array and using a bubble sort in the array.  Works fine for tiny series but for one with any substance...

Here is an example of a small series that generates the results that I want: http://www.gopherstateevents.com/series/series_results.asp?year=2014  
This series has three events, two of which have happened.  The Central Minnesota Series or the Lake Series will take forever to load.  I obviously need help with my process.  Here is what I am doing (I originally posted this on the asp section and they suggested I move it here for the sql experts to sound off on it):

1) The first recordset gets the number of records in the participant list to avoid re-dim preserve.  The second table gets the participants themselves, allowing for enoungh fields in the array for a possible 10 races:
    'get total by gender and dim array to avoid redim preserve
    Set rs = Server.CreateObject("ADODB.Recordset")
    If iAgeTo = 0 Then
        sql = "SELECT ParticipantID FROM SeriesParts WHERE SeriesID = " & lSeriesID & " AND Gender = '" & sGender & "'"
    Else
        sql = "SELECT ParticipantID FROM SeriesParts WHERE SeriesID = " & lSeriesID & " AND Gender = '" & sGender & "' AND Age >= " & iAgeFrom
        sql = sql & " AND Age <= " & iAgeTo
    End If
    rs.Open sql, conn, 1, 2
    If rs.RecordCount > 0 Then ReDim SeriesRslts(14, rs.RecordCount)
    rs.Close
    Set rs = Nothing

    i = 0
    Set rs = Server.CreateObject("ADODB.Recordset")
    If iAgeTo = 0 Then
        sql = "SELECT ParticipantID, PartName, Age, Gender FROM SeriesParts WHERE SeriesID = " & lSeriesID & " AND Gender = '" & sGender 
        sql = sql & "' ORDER BY PartName"
    Else
        sql = "SELECT ParticipantID, PartName, Age, Gender FROM SeriesParts WHERE SeriesID = " & lSeriesID & " AND Gender = '" & sGender & "' AND Age >= " 
        sql = sql & iAgeFrom & " AND Age <= " & iAgeTo & " ORDER BY PartName"
    End If
    rs.Open sql, conn, 1, 2
    Do While Not rs.EOF
        SeriesRslts(0, i) = rs(0).Value
        SeriesRslts(1, i) = Replace(rs(1).Value, "''", "'")
        SeriesRslts(2, i) = rs(2).Value
        SeriesRslts(3, i) = rs(3).Value
        For j = 4 To 14
            SeriesRslts(j, i) = "0"
        Next
        i = i + 1
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing

Open in new window


2) Next I go through the results table and get their points, based on their order of finish overall or within their age group.  Once I have their points I total them up (the last field in each array entry):
    For i = 0 To UBound(SeriesRslts, 2) - 1
        For j = 0 To UBound(SeriesRaces, 2) - 1
            If CDate(SeriesRaces(2, j)) > Date Then
                SeriesRslts(j + 4, i) = "0"
            Else
                SeriesRslts(j + 4, i) = GetMyPts(SeriesRaces(0, j), SeriesRslts(0, i), SeriesRslts(2, i))
            End If
        Next
    Next

    For i = 0 To UBound(SeriesRslts, 2) - 1
        For j = 4 To 13
'            If SeriesRslts(j, i) = "0" Then Exit For
            SeriesRslts(14, i) = CSng(SeriesRslts(14, i)) + CSng(SeriesRslts(j, i))
        Next
    Next

Open in new window


3) Finally, I sort:
    'sort the array
    For i = 0 To UBound(SeriesRslts, 2) - 2
        For j = i + 1 To UBound(SeriesRslts, 2) - 1
            If CSng(SeriesRslts(14, i)) < CSng(SeriesRslts(14, j)) Then
                For k = 0 To 14
                    SortArr(k) = SeriesRslts(k, i)
                    SeriesRslts(k, i) = SeriesRslts(k, j)
                    SeriesRslts(k, j) = SortArr(k)
                Next
            End If
        Next
    Next

Open in new window


I am not a dbadmin and I work primarily in classic asp and from within the sql server management console.  I have been told this is a job for a stored procedure which I have very little experience with.
0
Comment
Question by:Bob Schneider
  • 15
  • 14
  • 3
  • +1
34 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40204799
More importantly ... are you running Tough Mudder - Twin Cities this weekend?
0
 

Author Comment

by:Bob Schneider
ID: 40204827
Not running it...wish we were timing it. :)  Any leads?
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 34 total points
ID: 40204847
For starters, I don't see a question here.   What exactly are you asking us to do?

Second, if you're creating SQL via ASP, then you might as well do the sorting in SQL as well.
Seems a hell of a lot easier then sorting an array.
0
 

Author Comment

by:Bob Schneider
ID: 40204858
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.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 365 total points
ID: 40208146
Is this the type of output you want us to help with? results image
None of the SQL in this question would produce that result.

Can you provide the SQL you currently use to produce that result?
0
 

Author Comment

by:Bob Schneider
ID: 40209634
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

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 365 total points
ID: 40210164
I don't use/fully understand classic asp, but I can get the sense of what the code snippets you have provided are doing. However I can't provide a SQL based answer if I don't get the "big picture" which isn't yet available.

These are needed
> definition of the tables involved (table names and the fields they hold)
> sample data for each table
> an expected result (that relates to the sample data provided)

sample data can be very simple, e.g.

SeriesParts
ParticipantID, PartName, Age, Gender , SeriesID
1,string-data,12,m,100

and from that data I get the table name (SeriesParts) the field names (ParticipantID, PartName, Age, Gender , SeriesID) and the types of data they hold.

sample data also does not have to be vast in quantity

Ideally though the data in each table should allow joins to work.
0
 
LVL 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 101 total points
ID: 40210658
BobbaBuoy, if we can move what you are doing on the classic asp side to pure sql, you will get much better performance and speed.
0
 

Author Comment

by:Bob Schneider
ID: 40212878
Thanks for all of your patience with my ineptitude folks.  See if the attachment helps at all.
Series-Results-Data-Structure.docx
0
 
LVL 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 101 total points
ID: 40212895
I think what he is asking is to get the layout in text format.
Get LayoutYou will send up with something like below
CREATE TABLE [dbo].[tContacts](
	[ContactID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[HHID] [numeric](18, 0) NULL,
	[Prefix] [nvarchar](50) NULL,
	[FirstName] [nvarchar](30) NULL,
	[MiddleName] [nvarchar](30) NULL,
	[LastName] [nvarchar](30) NULL,
	[Suffix] [nvarchar](50) NULL,
	[Company] [nvarchar](50) NULL,

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 365 total points
ID: 40213060
Indeed. Sadly images are of very little assistance.

The data should also be in a reusable format, if there is private information (such as name, phone numbers, home address etc) then those fields should be scrubbed or ommitted
0
 

Author Comment

by:Bob Schneider
ID: 40214286
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 

Open in new window

0
 
LVL 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 101 total points
ID: 40214831
Can you also export a few rows of data?  If there is anything private, just change the names to protect the innocent...
0
 

Author Comment

by:Bob Schneider
ID: 40214859
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

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 365 total points
ID: 40215598
This is a great start. thanks.
However it isn't necessary to scrub the table definition (e.g. don't remove the field [PartName])
Also there seem to be more fields, line 16 of the definition ends with a comma

      [EventPl10] [int] NULL,

and the clustered index has no fields specified.

It is "safe" to leave the definition without edits.

The data however does need to be "scrubbed", so replacing names with "name1" name2" etc. is great.

However it is a good idea to include the column headings of the data otherwise there is some guesswork involved.
SeriesID	SeriesPartsID	ParticipantID	PartName	Age	Gender	EventPl1	EventPl2	EventPl3	EventPl4	EventPl5	EventPl6	EventPl7	EventPl8	EventPl9	EventPl10
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

Open in new window

From a combination of your table definitions and some data we can things like this: http://sqlfiddle.com/#!3/250a4/1
and, this then allows us to propose useful SQL to you as an answer.
0
 

Author Comment

by:Bob Schneider
ID: 40217024
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]

Open 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

Open 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

Open 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

Open 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
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 365 total points
ID: 40218295
Don't I also need the table [Series]? (& please include column headings on the data)

If you review the data below (or at http://sqlfiddle.com/#!3/12dd8/2 ) will I be able to join all the table together?
e.g. the participantids in SeriesParts aren't common to any other table

    select
    *
    from Participant
    

**[Results][2]**:
    
    | PARTICIPANTID | FIRSTNAME | LASTNAME |            CITY | ST |    PHONE |              EMAIL |                          DOB | GENDER | SENDINFO | CCLOGINVITE | SENDRSLTS | COMMENTS | COUNTRY | UPLOADED | TWITTER |  FBOOK |
    |---------------|-----------|----------|-----------------|----|----------|--------------------|------------------------------|--------|----------|-------------|-----------|----------|---------|----------|---------|--------|
    |            33 |    fname1 |   lname1 | White Bear Lake | MN | 555-6527 | dignan2934@aol.com | March, 15 1962 00:00:00+0000 |      M |        y |           n |         y |   (null) |  (null) |   (null) |  (null) | (null) |
    |            34 |    fname2 |   lname2 |        Somerset | WI | 555-5845 |             (null) | March, 15 1967 00:00:00+0000 |      F |        y |           n |         y |   (null) |  (null) |   (null) |  (null) | (null) |
    |            35 |    fname3 |   lname3 |    New Richmond | WI | 555-5305 |             (null) | March, 15 1954 00:00:00+0000 |      M |        y |           n |         y |   (null) |  (null) |   (null) |  (null) | (null) |
    |            36 |    fname4 |   lname4 |      Cumberland | WI | 555-8776 |             (null) | March, 15 1948 00:00:00+0000 |      M |        y |           n |         y |   (null) |  (null) |   (null) |  (null) | (null) |


**Query 2**:

    select
    *
    from SeriesParts
    

**[Results][3]**:
    
    | SERIESID | SERIESPARTSID | PARTICIPANTID |     PARTNAME | AGE | GENDER | EVENTPL1 | EVENTPL2 | EVENTPL3 | EVENTPL4 | EVENTPL5 | EVENTPL6 | EVENTPL7 | EVENTPL8 | EVENTPL9 | EVENTPL10 |
    |----------|---------------|---------------|--------------|-----|--------|----------|----------|----------|----------|----------|----------|----------|----------|----------|-----------|
    |       20 |             1 |          4461 |  Aaa, Gordon |  64 |      M |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |         0 |
    |       20 |             2 |          4462 |    Fff, Todd |  38 |      M |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |         0 |
    |       20 |             3 |          4463 |   Bbb, Steph |  20 |      F |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |         0 |
    |       20 |             4 |          4464 | Ccc, Michele |  34 |      F |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |        0 |         0 |


**Query 3**:

    select
    *
    from PartRace
    

**[Results][4]**:
    
    | PARTICIPANTID | PARTRACEID | AGE | AGEGRP | BIB | CLYDE | RCWLK | WHLCHR | INDDELAY | RACEID | TRANS1IN | TRANS1OUT | TRANS2IN | TRANS2OUT | LOCKED | WAVE |
    |---------------|------------|-----|--------|-----|-------|-------|--------|----------|--------|----------|-----------|----------|-----------|--------|------|
    |            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 |


**Query 4**:

    select
    *
    from IndResults
    

**[Results][5]**:
    
    | INDRSLTSID | PARTICIPANTID | EVENTPL | ELPSDTIME | FNLTIME | RACEID | ELIGIBLE | MYHISTID |    CHIPSTART |     CHIPTIME |
    |------------|---------------|---------|-----------|---------|--------|----------|----------|--------------|--------------|
    |         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 |



  [1]: http://sqlfiddle.com/#!3/12dd8/2

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Bob Schneider
ID: 40225256
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

Open 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
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 365 total points
ID: 40226460
Thanks!! I/we haven't lost interest either, but I will need to pester you about how to correctly interpret these tables. When will you have more time?
0
 

Author Comment

by:Bob Schneider
ID: 40226521
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!!!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40240967
BobbaBuoy,

Hi, are we still working on this?
0
 

Author Comment

by:Bob Schneider
ID: 40241232
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!
0
 

Author Comment

by:Bob Schneider
ID: 40242021
Just to be clear...ready whenever you are.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 365 total points
ID: 40242705
Right. You have been patient and have provided definitions of some tables, but what I would like to know is how you believe these tables relate to each other?It isn't completely obvious to me.

all I can see are these relationships:
[dbo].[SeriesParts] -> [dbo].[Series] via [SeriesID]

[dbo].[SeriesParts] -> [dbo].[Participant] via [ParticipantID]
[dbo].[PartRace] ->  [dbo].[Participant] via [ParticipantID]
[dbo].[IndResults] ->  [dbo].[Participant] via [ParticipantID]

Starting at the beginning, I need a series, then category & gender, then I should get the columns ("meets"?) and then results by participant. I don't know how to do this yet from the tables and data I see here.
0
 

Author Comment

by:Bob Schneider
ID: 40253814
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

Open 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

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 365 total points
ID: 40260617
Please refer to
http://www.experts-exchange.com/Programming/Languages/Scripting/ASP/Q_28497512.html#a40260611

I think that query structure may help us solve this question.
0
 

Author Comment

by:Bob Schneider
ID: 40261324
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

Open 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.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 365 total points
ID: 40261977
I see a loop for sorting

I thought the objective was to wean off using loops for sorting

and do the sorting in the SQL query

Instead I see a snowstorm of small SQL querieS (e.g via GetMyPts(lThisRaceID, lThisPartID))

But I don't think I can read that code sufficiently well to offer a single SQL alternative. I'll try.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 365 total points
ID: 40262175
I feel as if if the answer is here (someplace) but I can't see it and its frustrating.

You are using programmer's logic to combine data from the following:

1       SELECT sr.RaceID
        FROM SeriesRaces sr
        INNER JOIN SeriesEvents se ON sr.SeriesEventsID = se.SeriesEventsID
        WHERE se.SeriesID =  @lSeriesID  AND se.EventDate <  @Date  
        ORDER BY se.EventDate

2      SELECT sp.ParticipantID, sp.PartName, sp.Age
        FROM SeriesParts sp
        WHERE sp.SeriesID =  @lSeriesID  AND sp.Gender =  @sGender
        ORDER BY PartName

3       SELECT st.GndrPts
        FROM SeriesStdgs st
        INNER JOIN SeriesParts sp ON st.SeriesPartsID = sp.SeriesPartsID
        WHERE st.RaceID = @lThisRaceID
         AND sp.ParticipantID = @lThisPartID

I can see how 2 & 3 might be combined into 1 query (almost), but not all 3, I don't follow the programmic logic well enough to fill in the gaps.

I can't do this without your assistance, but I need the definitions for tables:
SeriesRaces, SeriesEvents and SeriesRaces

and a small amount of sample data from each of those, plus SeriesParts
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 365 total points
ID: 40262429
Here is what I now think is the "equivalent" SQL query for the script displayed at http://#a40261324

It uses a "CROSS JOIN" of the chosen races and the chosen participant list - basically "the mother array" of #races by #participants (the Cartesian product)

It left left joins that big matrix to results.

Finally is sorts the whole thing - I guessed at the fields to sort by
DECLARE @lSeriesID int
      , @Date datetime
      , @sGender varchar(1)

SET     @lSeriesID = 100
      , @Date      = '20140601'
      , @sGender   = 'M'         -- or 'F'

SELECT
      sr.RaceID
    , sp.ParticipantID
    , sp.PartName
    , sp.Age
    , st.GndrPts
FROM (
            SELECT
                  sr.RaceID
            FROM SeriesRaces sr
                  INNER JOIN SeriesEvents se
                              ON sr.SeriesEventsID = se.SeriesEventsID
            WHERE se.SeriesID = @lSeriesID
                  AND se.EventDate < @Date
      ) sr
      CROSS JOIN (
                  SELECT
                        sp.ParticipantID
                      , sp.PartName
                      , sp.Age
                      , sp.SeriesPartsID
                  FROM SeriesParts sp
                  WHERE sp.SeriesID = @lSeriesID
                        AND sp.Gender = @sGender
            ) sp
      LEFT JOIN SeriesStdgs st
                  ON sr.RaceID = st.RaceID
                        AND sp.SeriesPartsID = st.SeriesPartsID
ORDER BY
      sr.RaceID
      , sp.ParticipantID
;

Open in new window

0
 

Author Comment

by:Bob Schneider
ID: 40285706
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.
0
 
LVL 48

Expert Comment

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

Author Closing Comment

by:Bob Schneider
ID: 40285707
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!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40285782
Thanks BobbaBuoy, that page is lightning in comparison the older one, well done.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now