As we move to the new code for navigation and providing more information via the SQL Server databases and Exchange Server (which runs the conferencing), this page will allow you to look at how the code was created.  The first set of code has been published.  The code is for LSCPro a SDIFv3 data loader and maintenance tool written in VB6.0.  If you are interested in free code for development use by your organization look at LSCPro. As the Code examples are published feel free to ask questions and see if you can use it in your swimming site.  We do allow connections to our database servers for the use of the data.  We also encourage you to send SDIFv3 formatted meet results to be loaded into our times system.  for more information please send mail to jlorimer@lornet.com, you can also email results to this address.  The following is an article about the site in general and some of the database functionality that is being used.  The Second article is about how the site was templated andthe home page.

Currently all of the pages are written for ASP using vbscript and developed using Visual Interdev 6.0.  The future holds ASP.NET with all the code being rewritten for C#.   But that is not either here or there.  The site itself comprises of two servers one that holds the web pages and one that just does database.  Currently the site uses two databases, one for all of the results and proof of time, the other contains everything from athlete registration information to the news articles (i.e. kitchen drawer).  To understand the major function of the site requires knowledge about the design of the Times database and a few of the tables in the junk drwaer ( it's real name is SwimData).

The Times database contains 31 tables, some of those tables are not really being used on the site yet (Relay specific tables for instance).  The main tables used are tblSwimmers, tblSwimmerOnTeam, tblTeams, tblResults, tblMeets and tblSplits.  The table design was done so the data is very normalized.  This has drawbacks, mainly with trying to figure who swam what for whom when and what are the splits for that swim.  The other big issue has to deal with what parents, athletes and coaches want to see and that is the TopN reports.  We wrote a whole FAQ on this subject that is available in the FAQ section of this site.  Let's first talk about the table design and relationships.  Below is a figure that describes the table design and relationships.

So as far as the web site goes the majority of reports come out of using queries, stored procedures and views against this set of tables.  The simplest report to look at would be the all times report for an athlete.  It basically looks like:

SELECT tblResults.DistanceCode, tblResults.Stroke, tblResults.SwimTime, tblMeets.MeetName, tblResults.CourseCode, tblMeets.StartDate FROM tblResults INNER JOIN tblMeets ON tblResults.MeetID = tblMeets.MeetID WHERE (tblResults.SwimmerID = ?) AND (tblResults.SwimTime > 0) ORDER BY Times.dbo.tblResults.Stroke, Times.dbo.tblResults.DistanceCode, Times.dbo.tblResults.CourseCode, Times.dbo.tblResults.SwimTime

During the process of viewing the report the user provides the AthleteID and we run the query excluding DQ's and No Shows (these are stored in the database as times having a zero value.

A more complex query and report involves getting the best times for an athlete.  The best way to describe the query need is a non-equi Join.  What we do is actually create a view that computationally has the best performance times for all athletes covering all events and courses then select the one athlete that is requested.  the view has the following code:

SELECT R.EventAge, S.Sex, R.CourseCode, R.Stroke, R.DistanceCode, S.Lastname, S.Firstname, R.SwimTime AS besttime, S.SwimmerID AS AthID, R.TeamCode AS Club, R.ResultID, M.StartDate, S.Birthday FROM dbo.tblMeets M INNER JOIN dbo.tblResults R ON R.MeetID = M.MeetID AND R.SwimTime <> 0 INNER JOIN dbo.tblSwimmers S ON S.SwimmerID = R.SwimmerID LEFT OUTER JOIN dbo.tblResults R1 ON R1.SwimmerID = R.SwimmerID AND R1.CourseCode = R.CourseCode AND R1.DistanceCode = R.DistanceCode AND R1.Stroke = R.Stroke AND R1.SwimTime > 0 AND R1.SwimTime < R.SwimTime WHERE (R1.SwimmerID IS NULL)

and the selection for the report looks like this:

SELECT EventAge, Sex, CourseCode, Stroke, DistanceCode, Lastname, Firstname, besttime, AthID, Club, ResultID, StartDate FROM BestWithResultID WHERE (AthID = ?) AND (CourseCode = ?) AND (Stroke < 6) ORDER BY Stroke, DistanceCode

A quick note in the above query Stroke 6  and 7 are relays.  This last query displays only the best times for a given athlete and given course which are input parameters from the form.

OK it's time for the most complex of the reports and that is TopN.  Again there is a FAQ on the report itself elsewhere on the site.  I am only going to describe the TopN 90 day code. This was the original report we had on the site for TopN.  It uses a 90 day window with the last day being the date the report is run.  It was originally intended to give a little better view of who is swimming the fastest right now, instead of looking at a whole season.  The report is currently based off two views and a standalone query.  The first view isolates all data in the 90 day window:

SELECT tblSwimmers.Lastname, tblSwimmers.Firstname, tblSwimmers.Sex, tblSwimmers.Birthday, tblSwimmers.SwimmerID, tblResults.CourseCode, tblResults.EventAge, tblResults.DistanceCode, tblResults.Stroke, tblResults.SwimTime, tblResults.TeamCode, tblMeets.MeetName, tblMeets.StartDate, tblResults.ResultID FROM dbo.tblSwimmers INNER JOIN dbo.tblResults ON dbo.tblSwimmers.SwimmerID = dbo.tblResults.SwimmerID INNER JOIN dbo.tblMeets ON dbo.tblResults.MeetID = dbo.tblMeets.MeetID WHERE (dbo.tblMeets.StartDate > DATEADD(dd, - 90, GETDATE()))

All it does is collect a flattened set (assembled from all the necessary tables) of data from the last 90 days.  The next query is the Non Equi-Join which basically does a query on the previous query against itself to find the fastest times for each Athlete ID:

SELECT R.EventAge, R.Sex, R.CourseCode, R.Stroke, R.DistanceCode, R.Lastname, R.Firstname, R.SwimTime AS besttime, R.SwimmerID AS AthID, R.TeamCode AS Club, R.ResultID, R.StartDate, R.Birthday FROM dbo.results90day R LEFT OUTER JOIN dbo.results90day R1 ON R1.SwimmerID = R.SwimmerID AND R1.CourseCode = R.CourseCode AND R1.DistanceCode = R.DistanceCode AND R1.Stroke = R.Stroke AND R1.SwimTime > 0 AND R1.SwimTime < R.SwimTime WHERE (R1.SwimmerID IS NULL) AND (R.SwimTime > 0)

This query provides the best acheived times for each athlete in a given course, Distance, Stroke again eliminating the DQ's and No Shows.  The next is the query (in this case a stored procedure) actually fired from the ASP page which queries the previous view:

Alter Procedure TopN3 @Sex char(1), @Agegroup char(4), @Course char(1), @Stroke int, @Distance int /* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT ) */ As SELECT Top 100 Lastname, Firstname, besttime, AthID, Club, ResultID /* FROM Best90Day */ FROM Results90DayBest WHERE (sex = @Sex) AND (EventAge = @Agegroup) AND (CourseCode = @Course) AND (Stroke = @Stroke) AND (DistanceCode = @Distance) ORDER BY besttime /* set nocount on */ return

We are actually only taking the Top 100 results but allow the user to only display a subset.  The results are ordered in increasing time.

So what problems have we run into.  The biggest one is the dirty data problem.  On any given meet a particular Athlete may be entered with a varying Athlete ID due to typo-graphic errors or the coach/ parent/entry chair didn't know so they made one up.  THis causes major headaches as suddenly the same Athlete has multiple ID's and can show up in multiple places in a report where they should have appeared only once.  The other dirty data problem is that clubs often forget to put their LSC code on their entries, so poof their data is gone when we import Colorado (CO).

I think that is enough on this particular article.  The second article describes how the site was put together via a template and a description of the home page .