title: Trying to build an SQL statement for complex search scenario (Comparison)
body: <p>I am trying to build an SQL statement for a complex search scenario and I need you guys help. The scenario is:</p>
  
  <p>I am trying to provide a service where Merchants will be signing up for it and provide the best value (most money) they will be offering for each product and then Users can provide their location and provide how much/how many kg of the each product they have and enter their values to a input form and then I want my SQL to list all the Merchants with the amount of money they will be offering in TOTAL where the most will start from the top.</p>
  
  <p>I have a database with Merchants table &amp; MerchantProducts table and Locations table. Every merchant have 20 products, and these products are same for every Merchant, </p>
  
  <pre><code>Merchant Table I have:
  UserID (which is merchant's unique id)
  MerchantName
  MerchantTelNo
  MerchantIsActive
  
  MerchantProducts Table I have:
  MerchantProductID
  UserID
  MerchantProductName
  MerchantProductValue
  MerchantProductLastUpdated
  </code></pre>
  
  <p>What I am trying to do is:</p>
  
  <p>1- I have a Product form where users can enter the amount of product they have. Which is a list of products only includes a Name of the Product(Label) and Amount they have(textbox). The amount of products will be multiplied with the value of product.</p>
  
  <p>2- User will enter his/her location.</p>
  
  <p>3- SQL fill find the Merchants nearby and will list them where the most amount will be listed at top and the most least below it.</p>
  
  <p>So far I have accomplished where a user will choose only one product and enter their location and I will get a return of one Merchant with the most amount offered. I have accomplished this with the following SQL Stored Procedure:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[GetNearbyLocations2] 
  @ChosenProduct VARCHAR(100),
  @CenterLatitude FLOAT, 
  @CenterLongitude FLOAT, 
  @SearchDistance FLOAT, 
  @EarthRadius FLOAT AS DECLARE @CntXAxis FLOAT DECLARE @CntYAxis FLOAT DECLARE @CntZAxis FLOAT 
  SET @CntXAxis = COS(RADIANS(@CenterLatitude)) * COS(RADIANS(@CenterLongitude)) 
  SET @CntYAxis = COS(RADIANS(@CenterLatitude)) * SIN(RADIANS(@CenterLongitude)) 
  SET @CntZAxis = SIN(RADIANS(@CenterLatitude)) 
  
  
  SELECT TOP 100 *,
  ProxDistance = @EarthRadius * ACOS( dbo.XAxis(latitude, longitude)*@CntXAxis + dbo.YAxis(latitude, longitude)*@CntYAxis + dbo.ZAxis(latitude)*@CntZAxis) 
  FROM MerchantProducts AS M INNER JOIN Locations AS L ON M.UserID = L.UserID INNER JOIN (SELECT MAX(M.MerchantProductValue) AS MerchantProductValue FROM Locations AS L LEFT OUTER JOIN MerchantProducts AS M On L.UserID = M.UserID 
  WHERE (M.MerchantProductName = @ChosenProduct AND @EarthRadius * ACOS( dbo.XAxis(latitude, longitude)*@CntXAxis + dbo.YAxis(latitude, longitude)*@CntYAxis + dbo.ZAxis(latitude)*@CntZAxis) &lt;= @SearchDistance))
  AS MX ON M.MerchantProductValue = MX.MerchantProductValue
  AND M.MerchantProductName = @ChosenProduct
  AND latitude IS NOT NULL ORDER BY ProxDistance ASC
  </code></pre>
  
  <p>I am trying to create a stored procedure to accomplish no. 1,2 &amp; 3. Basically I want the users to enter how many kg of that product they have for each product and then I want sql to go through each Merchant and find out which merchant will give the most TOTAL amount of money for all of those products that a user entered value for in a list.</p>
  
  <p>Thanks in advance and I hope I got my question through.</p>