sql server 2008 - Return a Single Value based on 3 values passed into a query -
i need little help.
i have table, contains parameters determine customers rating.
table structure 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 values have stored in table:
excellent | 20 | 99999 | 0 | 0 | null | null | 20 | 99999 | 0 | 99999 | 0 | 2 | 1 | 19 | 0 | 2 | null | null | 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 need able is:
in stored procedure need pass in 3 values:
- redeemedcnt int
- disposedcnt int
- recentdisposedcnt int
based on these values passed in, want return single rating:
for example, if pass in:
redeemedcnt = 35
disposedcnt = 0
recentdisposedcnt = 0
then returned rating should excellent
if pass in:
redeemedcnt = 35
disposedcnt = 20
recentdisposedcnt = 2
then returned rating should good
if pass in:
redeemedcnt = 35
disposedcnt = 20
recentdisposedcnt = 0
then returned rating should average
the examples above derived our business rules, trying set stored procedure based on above table values, make more flexible.
i have made start on query, seem having issues clause not return single result:
declare @redeemedcnt int = 35 declare @disposedcnt int = 0 declare @recentdisposedcnt int = 0 select rating customerratingparameters (redeemedmin <= @redeemedcnt , redeemedmax >= @redeemedcnt) , (disposedmin <= @disposedcnt , disposedmax >= @disposedcnt) , (recentdisposedmin <= @recentdisposedcnt , recentdisposedmax >= @recentdisposedcnt) do need conditional clause or entire statement need conditional?
please can offer assistance.
cheers.
update
since change coming due moving code c# code db, thought may useful provide 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 disposal date within last 2 years. var recentlydisposed = p in disposed 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; }
you have several reasons of returning multiple results:
- you should avoid
nullvalues incustomerratingparametersor should checknullvalues inwhereclause. - you should check value ranges not intersected. e.g., take @ configuration of
poor- matches initial values.
the whole idea of configuring in db such type of calculations - need not change software when conditions change. can go further , redesign db case when business needs more criterias rating calculation, e.g. visitedcnt, rejectedcnt, etc., etc. should example:
- add table parameter types, e.g.
customerratingparametertypes- redeemed, disposed, recentdisposedcnt, etc. - modify table
customerratingparametersfollowing structure: parametertype, rating, parametermin, parametermax.
so can pass table variable parameter stored procedure contains needed source pairs parametertype/parametervalue.
update , update-2
add priority column. table structure becomes 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 | | 20 | 99999 | 0 | 99999 | 0 | 2 3 | | 1 | 19 | 0 | 2 | null | null 4 | | 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 1 record rating customerratingparameters (redeemedmin <= @redeemedcnt , redeemedmax >= @redeemedcnt) , (disposedmin <= @disposedcnt , disposedmax >= @disposedcnt) , (recentdisposedmin <= @recentdisposedcnt , recentdisposedmax >= @recentdisposedcnt) order priority asc -- order records priority 
Comments
Post a Comment