Help with query

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
GOPlease send the table DDL and sample data as INSERT statements, and what the
expected output looks like. Without that information, we are guessing and
the quality of help is sub-optimal..
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"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
Why is everything in your world an identifier? Explain what an
"age_id" is? Likewise, what is a gender_id? Gee, everyone else uses
an ISO gender_code.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.