Help with query

24 Jun 2006 2:09 PM

Niclas

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

24 Jun 2006 2:23 PM

Arnie Rowland

Please 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

Show quote

"Niclas" <lindblom_nic***@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
>
>

25 Jun 2006 12:21 AM

--CELKO--

> @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.