Databases
StackOverflow2013
Postsdbo
POReturn a Single Value based on 3 values passed into a query
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POReturn a Single Value based on 3 values passed into a query
    text
    copied!<p>I need a little help.</p> <p>I have a table, which contains parameters to determine a customers rating.</p> <p>Table structure is as follows:</p> <pre><code>Column Name | Data Type | Allow Nulls ----------------------------------------------- Rating | varchar(10) | N RedeemedMin | int | Y RedeemedMax | int | Y DisposedMin | int | Y DisposedMax | int | Y RecentDisposedMin | int | Y RecentDisposedMax | int | Y </code></pre> <p>Here are the values I have stored in the table:</p> <pre><code>Excellent | 20 | 99999 | 0 | 0 | NULL | NULL Good | 20 | 99999 | 0 | 99999 | 0 | 2 Good | 1 | 19 | 0 | 2 | NULL | NULL Good | 4 | 99999 | 0 | 0 | NULL | NULL Average | 20 | 99999 | 3 | 99999 | NULL | NULL Average | 1 | 19 | 3 | 99999 | NULL | NULL Poor | 0 | 0 | 1 | 99999 | NULL | NULL Poor | NULL | NULL | 0 | 99999 | 4 | 99999 New_ | 0 | 0 | 0 | 0 | NULL | NULL </code></pre> <p>What I need to be able to do is:</p> <p>In a Stored Procedure I need to pass in 3 Values:</p> <ul> <li>RedeemedCnt <em>int</em></li> <li>DisposedCnt <em>int</em></li> <li>RecentDisposedCnt <em>int</em></li> </ul> <p>Based on these values passed in, I want to return a single Rating:</p> <p>For Example, if I pass in:</p> <blockquote> <p>RedeemedCnt = 35</p> <p>DisposedCnt = 0 </p> <p>RecentDisposedCnt = 0</p> </blockquote> <p>Then the returned rating should be <strong><em>Excellent</em></strong></p> <p>If I pass in:</p> <blockquote> <p>RedeemedCnt = 35</p> <p>DisposedCnt = 20</p> <p>RecentDisposedCnt = 2</p> </blockquote> <p>Then the returned rating should be <strong><em>Good</em></strong> </p> <p>If I pass in:</p> <blockquote> <p>RedeemedCnt = 35</p> <p>DisposedCnt = 20</p> <p>RecentDisposedCnt = 0</p> </blockquote> <p>Then the returned rating should be <strong><em>Average</em></strong></p> <p>The examples above are derived from our business rules, but I am trying to set this up as a stored procedure based on the above table values, to make this more flexible.</p> <p>I have made a start on the query, but seem to be having issues with the WHERE clause as it does not return a single result:</p> <pre><code>DECLARE @redeemedCnt int = 35 DECLARE @disposedCnt int = 0 DECLARE @recentDisposedCnt int = 0 SELECT Rating FROM CustomerRatingParameters WHERE (RedeemedMin &lt;= @redeemedCnt AND RedeemedMax &gt;= @redeemedCnt) AND (DisposedMin &lt;= @disposedCnt AND DisposedMax &gt;= @disposedCnt) AND (RecentDisposedMin &lt;= @recentDisposedCnt AND RecentDisposedMax &gt;= @recentDisposedCnt) </code></pre> <p>Do I need a conditional WHERE clause or does the entire statement need to be conditional?</p> <p>Please can someone offer some assistance.</p> <p>Cheers.</p> <hr> <h2>UPDATE</h2> <p>Since this change is coming about due to moving code from C# code into the DB, I thought it may be useful to provide the current C# IF... statement:</p> <pre><code>public CustomerRating GetCustomerRating(int customerID) { CustomerRating cr = CustomerRating.None; IList&lt;Item&gt; redeemed; IList&lt;Item&gt; disposed; int countRedeemed = 0; int countDisposed = 0; int countRecentlyDisposed = 0; DateTime twoYearsAgo = DateTime.Now.AddYears(-2); try { redeemed = GetItems(customerID, "R"); disposed = GetItems(customerID, "D"); countRedeemed = redeemed.Count(); countDisposed = disposed.Count(); // Select items where disposal date is within the last two years. var recentlyDisposed = from p in disposed where p.DisposedDate.HasValue &amp;&amp; p.DisposedDate.Value.Date &gt; twoYearsAgo.Date select p; countRecentlyDisposed = recentlyDisposed.Count(); if (countRedeemed &gt;= 20) { if (countDisposed == 0) { cr = CustomerRating.Excellent; } else if (countRecentlyDisposed &lt; 3) { cr = CustomerRating.Good; } else if (countDisposed &gt;= 3) { cr = CustomerRating.Average; } } else if (countRedeemed &gt;= 1 &amp;&amp; countRedeemed &lt;= 19) { if (countDisposed &lt; 3) { cr = CustomerRating.Good; } else if (countDisposed &gt;= 3) { cr = CustomerRating.Average; } } else if (countRedeemed &gt;= 4 &amp;&amp; countRedeemed &lt;= 99999) { if (countDisposed == 0) { cr = CustomerRating.Good; } } else if (countRedeemed == 0) { if (countDisposed == 0) { cr = CustomerRating.New_; } else if (countDisposed &gt; 0) { cr = CustomerRating.Poor; } } if (countRecentlyDisposed &gt;= 3) { cr = CustomerRating.Poor; } } catch (Exception) { //throw; } return cr; } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload