"Niclas" <lindblom_niclas@hotmail.com> wrote in message
news:%23roRJf5lGHA.1488@TK2MSFTNGP02.phx.gbl...
> Hi,
>
> I have a stored procedure (posted below) that returns a club ranking list
> with fatsest to slowest time for a swim club based on
> Stroke,Distance,Course,Gender,Age. Number of rows in the ranking is based
> on the @Rowcount variable passed in.
>
> I would like to expand this query to return a the (single) fastest time
> per strokeID held in the BBMD_Strokes table. I understand that I could use
> a CURSOR with a SELECT StrokeID from BBMD_strokes and loop through the
> same query , but have read that CURSORS should be avoided due to poor
> performance. Is there a prefered option to solve this ?
>
> Niclas
>
> CREATE procedure dbo.BBMD_GetEventRecord
>
> @StrokeID int,
> @DistanceID int,
> @CourseID int,
> @GenderID int,
> @AgeID int,
> @RowCount int
>
> AS
>
> Set ROWCOUNT @Rowcount
> SELECT D.DistanceName + ' ' + S.StrokeName as EventName,U.LastName + ', '
> + U.firstname as Swimmer,
> R.Result,G.GalaName, G.StartDate,X.DOB
> FROM BBMD_Results R
> JOIN BBMD_Events E ON R.Eventid=E.EventID
> JOIN BBMD_Galas G ON R.GalaID=G.GalaID
> JOIN BBMD_Strokes S ON E.strokeID=S.strokeID
> JOIN BBMD_Distances D ON E.DistanceID=D.DistanceID
> JOIN Users U ON R.UserID=U.UserID
> JOIN BBMD_ExtUser X ON R.USERID=X.UserID
> JOIN (SELECT R.UserID,MIN(R.Result) as RES
> FROM BBMD_Results R
> JOIN BBMD_Events E ON R.EventID=E.EventID
> JOIN BBMD_ExtUser X ON R.UserID=X.UserID
> JOIN BBMD_Galas G ON R.GalaID=G.GalaID
> WHERE
>
> E.StrokeID=@StrokeID AND
> E.Distanceid=@DistanceID AND
> E.Genderid=@GenderID AND
> E.Courseid=@CourseID AND
> R.Resulttypeid=1 AND
>
> DATEDIFF (YEAR, X.DOB, G.StartDate ) - CASE
> WHEN 100 * MONTH(G.StartDate) + DAY(G.StartDate)
> < 100 * MONTH(X.DOB) + DAY(X.DOB)
> THEN 1 ELSE 0 END
> BETWEEN (SELECT YearMin From BBMD_YearGroups
> WHERE YearGroupID= @Age)
> AND
> (SELECT YearMax From BBMD_YearGroups WHERE YearGroupID= @AgeID)
>
> Group By R.UserID) AS MinR ON minR.Res=R.result
> AND minR.UserID=R.UserID
>
> GROUP BY U.Lastname,U.firstname,G.GalaName, MinR.Res,R.Result,
> S.StrokeName,D.DistanceName, G.StartDate,X.DOB
>
> ORDER BY RESULT
> GO
>
>

> @StrokeID int,
> @DistanceID int,
> @CourseID int,
> @GenderID int,
> @AgeID int,
> @RowCount int