title: SQL Rating Score System
body: <p>I have a table called tbl_Banners.  This table stores banners to display on my website.  Attached to this table is a field for zip code (ba_zip_code).  The zip code is used to display banners closest to the users viewing the site.  I also have a relational table called tbl_Banners_links that stores categories for different types of vehicles.  This table contains the following fields:</p>
  
  <pre><code>bal_id (unique ID), ba_id (banner ID), and vc_id (vehicle category ID)
  </code></pre>
  
  <p>Each banner can have multiple vehicle category links (such as Car, Truck, SUV, etc.)</p>
  
  <p>I want to provide a rating score for certain criteria, including miles from users location, vehicle category, and banner priority (ba_priority).</p>
  
  <p>Here is the result set for my data and scores that have been applied for each banner, based upon the SQL statement BELOW this table:</p>
  
  <p><img src="https://i.stack.imgur.com/vPJVP.gif" alt="enter image description here"></p>
  
  <pre><code>SELECT ba.ba_id, ba.ba_url, ba.ba_filename, bal.vc_id,
  (CASE WHEN ba.ba_priority = 1 THEN 40 ELSE 0 END) As ba_priority, 
  (CASE WHEN bal.vc_id = 1 THEN 50 ELSE 0 END) As vc_id_score, 
  (CASE 
  WHEN dbo.CalcDistancebetweenGeoLocations(45.751671, -87.08951, [ba_latitude], [ba_longitude], 0) &lt;= 30 THEN 40 
  WHEN dbo.CalcDistancebetweenGeoLocations(45.751671, -87.08951, [ba_latitude], [ba_longitude], 0) &gt; 30 AND dbo.CalcDistancebetweenGeoLocations(45.751671, -87.08951, [ba_latitude], [ba_longitude], 0) &lt;= 60 THEN 30 
  WHEN dbo.CalcDistancebetweenGeoLocations(45.751671, -87.08951, [ba_latitude], [ba_longitude], 0) &gt; 60 AND dbo.CalcDistancebetweenGeoLocations(45.751671, -87.08951, [ba_latitude], [ba_longitude], 0) &lt;= 100 THEN 20 
  WHEN dbo.CalcDistancebetweenGeoLocations(45.751671, -87.08951, [ba_latitude], [ba_longitude], 0) &gt; 100 THEN 0 ELSE 0 END) As ba_distance 
  INTO #MyTempBannerTable 
  FROM tbl_Banners ba 
  LEFT JOIN tbl_Banners_links bal ON bal.ba_id = ba.ba_id 
  WHERE ba.ba_active = 1 AND ba.ba_c_id = 1;
  </code></pre>
  
  <p>The rating score works like such (or I want it to):</p>
  
  <pre><code>ba_priority = 1 = 40 points
  mileage 0-29 = 40 points
  mileage 30-59 = 30 points
  mileage 60-99 = 20 points
  mileage 100+ = 0 points
  vc_id = selected vc_id = 50 points
  </code></pre>
  
  <p>I need the code to add up all of these and total for each banner based upon the criteria.  As you can see from the result set below, if a banner has multiple vc_id listed, it TOTALS all of the scores for each vc_id for that particular banner.  It should only grab the unique banner score.  So a banner that has the following:</p>
  
  <pre><code>mileage 0-29 = 40
  ba_priority = 40
  vc_id = 50
  Total = 130 points
  </code></pre>
  
  <p>Instead, it is totaling up to 890 points.</p>
  
  <p>I need to order by TOTAL SCORE and grab just the top 5 banners, using this code:</p>
  
  <pre><code>SELECT TOP 5 ba_id, ba_url, ba_filename, SUM(vc_id_score + ba_distance + ba_priority) As RatingScore 
  FROM #MyTempBannerTable 
  GROUP BY ba_id, ba_url, ba_filename 
  ORDER BY RatingScore DESC, NEWID()
  </code></pre>
  
  <p>Here is the result set:</p>
  
  <p><img src="https://i.stack.imgur.com/MclgQ.gif" alt="enter image description here"></p>
  
  <p>I hope I made sense and didn't confuse you and myself with this issue.</p>