CODEWITHSUNDEEP
sql-server-2008stored-procedureswhere-clause
Richard Gale
Richard Gale

Reputation: 1952

Return a Single Value based on 3 values passed into a query

I need a little help.

I have a table, which contains parameters to determine a customers rating.

Table structure is as follows:

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

Here are the values I have stored in the table:

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

What I need to be able to do is:

In a Stored Procedure I need to pass in 3 Values:

Based on these values passed in, I want to return a single Rating:

For Example, if I pass in:

RedeemedCnt = 35

DisposedCnt = 0

RecentDisposedCnt = 0

Then the returned rating should be Excellent

If I pass in:

RedeemedCnt = 35

DisposedCnt = 20

RecentDisposedCnt = 2

Then the returned rating should be Good

If I pass in:

RedeemedCnt = 35

DisposedCnt = 20

RecentDisposedCnt = 0

Then the returned rating should be Average

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.

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:

DECLARE @redeemedCnt int = 35
DECLARE @disposedCnt int = 0
DECLARE @recentDisposedCnt int = 0

SELECT
    Rating
FROM
    CustomerRatingParameters
WHERE       
    (RedeemedMin <= @redeemedCnt AND RedeemedMax >= @redeemedCnt) AND
    (DisposedMin <= @disposedCnt AND DisposedMax >= @disposedCnt) AND
    (RecentDisposedMin <= @recentDisposedCnt AND RecentDisposedMax >= @recentDisposedCnt)

Do I need a conditional WHERE clause or does the entire statement need to be conditional?

Please can someone offer some assistance.

Cheers.

UPDATE

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:

public CustomerRating GetCustomerRating(int customerID)
{
    CustomerRating cr = CustomerRating.None;

    IList<Item> redeemed;
    IList<Item> 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 && p.DisposedDate.Value.Date > twoYearsAgo.Date
                               select p;

        countRecentlyDisposed = recentlyDisposed.Count();

        if (countRedeemed >= 20)
        {
            if (countDisposed == 0)
            {
                cr = CustomerRating.Excellent;
            }
            else if (countRecentlyDisposed < 3)
            {
                cr = CustomerRating.Good;
            }
            else if (countDisposed >= 3)
            {
                cr = CustomerRating.Average;
            }
        }
        else if (countRedeemed >= 1 && countRedeemed <= 19)
        {
            if (countDisposed < 3)
            {
                cr = CustomerRating.Good;
            }
            else if (countDisposed >= 3)
            {
                cr = CustomerRating.Average;
            }
        }
        else if (countRedeemed >= 4 && countRedeemed <= 99999)
        {
            if (countDisposed == 0)
            {
                cr = CustomerRating.Good;
            }
        }
        else if (countRedeemed == 0)
        {
            if (countDisposed == 0)
            {
                cr = CustomerRating.New_;
            }
            else if (countDisposed > 0)
            {
                cr = CustomerRating.Poor;
            }
        }

        if (countRecentlyDisposed >= 3)
        {
            cr = CustomerRating.Poor;
        }
    }
    catch (Exception)
    {
        //throw;
    }

    return cr;
}

Upvotes: 0

Views: 124

Answers (1)

Serg
Serg

Reputation: 2427

You have several reasons of returning multiple results:

  1. You should avoid NULL values in CustomerRatingParameters or should check for NULL values in your WHERE clause.
  2. You should check that your value ranges are not intersected. E.g., take a look at your configuration of Poor - it matches any initial values.

The whole idea of configuring in DB such type of calculations is very good - you need not change your software when conditions change. But you can go further and redesign your DB even for the case when business needs more criterias for rating calculation, e.g. VisitedCnt, RejectedCnt, etc., etc. For this you should as an example:

  • add a table for parameter types, e.g. CustomerRatingParameterTypes - Redeemed, Disposed, RecentDisposedCnt, etc.
  • modify a table CustomerRatingParameters to the following structure: ParameterType, Rating, ParameterMin, ParameterMax.

So you can pass a table variable as parameter to your stored procedure which contains all needed source pairs ParameterType/ParameterValue.

UPDATE and UPDATE-2

Add Priority column. Table structure becomes as follows:

Column Name       |  Data Type   |  Allow Nulls
-----------------------------------------------
Priority          |  int         |      N
Rating            |  varchar(10) |      N
RedeemedMin       |  int         |      Y  
RedeemedMax       |  int         |      Y
DisposedMin       |  int         |      Y
DisposedMax       |  int         |      Y
RecentDisposedMin |  int         |      Y
RecentDisposedMax |  int         |      Y

Data changed to:

1 |    Excellent  |   20     |   99999  |   0      |   0      |   NULL   |   NULL
2 |    Good       |   20     |   99999  |   0      |   99999  |   0      |   2
3 |    Good       |   1      |   19     |   0      |   2      |   NULL   |   NULL
4 |    Good       |   4      |   99999  |   0      |   0      |   NULL   |   NULL
5 |    Average    |   20     |   99999  |   3      |   99999  |   NULL   |   NULL
6 |    Average    |   1      |   19     |   3      |   99999  |   NULL   |   NULL
7 |    Poor       |   0      |   0      |   1      |   99999  |   NULL   |   NULL
8 |    Poor       |   NULL   |   NULL   |   0      |   99999  |   4      |   99999
9 |    New_       |   0      |   0      |   0      |   0      |   NULL   |   NULL

SELECT statement:

DECLARE @redeemedCnt int = 35
DECLARE @disposedCnt int = 0
DECLARE @recentDisposedCnt int = 0

SELECT TOP(1)    -- Select only one record
    Rating
FROM
    CustomerRatingParameters
WHERE       
    (RedeemedMin <= @redeemedCnt AND RedeemedMax >= @redeemedCnt) AND
    (DisposedMin <= @disposedCnt AND DisposedMax >= @disposedCnt) AND
    (RecentDisposedMin <= @recentDisposedCnt AND RecentDisposedMax >= @recentDisposedCnt)
ORDER BY Priority ASC    -- Order records with Priority

Upvotes: 1

Related Questions