SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru Raskolnikov's Avatar
    Join Date
    Jul 2003
    Location
    USA
    Posts
    606
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS SQL Query Help

    I have this query.

    I am really new with MS SQL and wanted to know if there was a more efficient way to accomplish this.

    Seems that there would be an easier way instead of doing the math each time.

    Code SQL:
    SELECT p.playerId AS golferId,
    	   SUBSTRING(p.FIRST,1,1) + '. ' + p.LAST AS golferName,
    	   ps.round1+ps.round2+ps.round3+ps.round4 AS lngScore,
    	   t.currentRound*t.par18 AS lngPar,
    	   CASE 
    			WHEN (ps.round1+ps.round2+ps.round3+ps.round4-(t.currentRound*t.par18))<0 THEN CAST(ps.round1+ps.round2+ps.round3+ps.round4-(t.currentRound*t.par18) AS nvarchar) 
    			WHEN (ps.round1+ps.round2+ps.round3+ps.round4-(t.currentRound*t.par18))=0 THEN 'E'
    			WHEN (ps.round1+ps.round2+ps.round3+ps.round4-(t.currentRound*t.par18))>0 THEN '+'+CAST(ps.round1+ps.round2+ps.round3+ps.round4-(t.currentRound*t.par18) AS nvarchar)
    	   END AS lngParScore,
    	   ps.rank AS lngRank
    FROM playerscores ps 
    JOIN players p ON p.playerId = ps.playerId
    JOIN tournaments t 
    ON ps.tournamentId=t.tournamentId 
    WHERE t.active='1' AND madeTheCut!='0'

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    "doing the math each time" ???

    the only alternative would be to ~store~ the results of the calculation, which seems rather safe to do, once the scores for each round have been entered (if a score needs to be revised, then of course the calculated score would also need to be revised)

    but the calculations take only a few nanoseconds, right? so where's the pain?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    You could write a User Defined Function to help the query engine here, but that probably won't materially effect things unless you are talking lots and lots of data. More data than one would probably touch in a web app.

  4. #4
    SitePoint Guru Raskolnikov's Avatar
    Join Date
    Jul 2003
    Location
    USA
    Posts
    606
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is a good point. they probably do not take much time.

    I am actually going through some old .net code and taking places that had alot of c#.net while, and for loops and rewriting the sql to remove those loops and eleviate alot of the webserver stress and in stead put it onto the db server. While doing this i wanted to make sure that every sql statment i made was as efficient as i could make it that way i would not have to revisit it anytime soon.

    Ras


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •