title: Find nearest Number when one of the fields is null but you need a Value
body: <p>I am working on a report that gives me a Monthly Rate for each Generator we have based on the GenAmps and GenVolts. If My Pricing table has the GenAmps # and GenVolt size then it will populate a Monthly Rate. There are cases as you can see below when there is a GenAmps # and GenVolts value that are not found in my Pricing table. When this occurs i want the MonthlyRate to pull from the closest GenAmps # where the GenVolts value equal, ex(<strong>GenAmps</strong> 15, <strong>GenVolts</strong> KW) which is not found in the pricing table, so i want it to pull the MonthlyRate from the (<strong>GenAmps</strong> 20, <strong>GenVolts</strong> KW)</p>
  
  <pre><code>GenAmps GenVolts    PricingGenAmp   PricingGenVolt  MonthRate(160hr)
  10       KW           NULL              NULL           NULL
  15       KVA          NULL              NULL           NULL
  </code></pre>
  
  <p>This is what my report looks like if they cannot find the GenAmps # and GenVolts value in the pricing table. </p>
  
  <pre><code>GenAmps GenVolts    MonthRate(160hr)
    25      KVA         1251
    20      KW          1251
  </code></pre>
  
  <p>(Seen above is from the pricing table). Since these are not in the pricing table i want the 10KW to pull the Monthly rate from the 20 KW and the 15 KVA from the 25 KVA. So it would look like this: </p>
  
  <pre><code>GenAmps GenVolts    PricingGenAmp   PricingGenVolt  MonthRate(160hr)
  10       KW           20                KW              1251
  15       KVA          25                KVA             1251
  </code></pre>
  
  <p>It should look like the data above when the query is working correctly. </p>
  
  <p>There are GenAmps # that range from 10 - 900 that are not found in the pricing table.</p>
  
  <p>this is the Query that i currently have, which needs to be revised so i can make the data look the way i want it to look.</p>
  
  <pre><code>SELECT        TOP (100) PERCENT ContractID, SaleRep, GenAmps, GenVolts, GenPhase, EstRunningHours, MonthlyRate, DeliveryRate, PickupRate, FuelRate, DropCharge, HourlyServiceRate, 
                           CASE WHEN gr.PricingGenAmps IS NULL THEN
                               (SELECT        MAX(GenAmps) AS a1
                                 FROM            dbo.GeneratorSizePrices AS a
                                 WHERE        (GenAmps &lt; gr.GenAmps) AND a.GenVolts = gr.GenVolts) ELSE gr.PricingGenAmps END AS PricingGenAmp, ISNULL([MonthRate(160hr)],
                               (SELECT        [MonthRate(160hr)]
                                 FROM            dbo.GeneratorSizePrices AS b
                                 WHERE        (GenAmps =
                                                               (SELECT        MAX(GenAmps) AS a
                                                                 FROM            dbo.GeneratorSizePrices
                                                                 WHERE        (GenAmps &lt; gr.GenAmps) AND (GenVolts = gr.GenVolts))))) AS Month
  FROM            dbo.Elliott_GensRentalRate AS gr
  GROUP BY ContractID, SaleRep, GenAmps, GenVolts, GenPhase, EstRunningHours, MonthlyRate, DeliveryRate, PickupRate, FuelRate, DropCharge, HourlyServiceRate, PricingGenAmps, [MonthRate(160hr)]
  ORDER BY PricingGenAmps, GenAmps
  </code></pre>