I am working on a counter(KPI) system .The system calculates many indicators of different business logic but which are all numeric values.
For each counter range criteria a score is assigned .
For example for the counter N1 if the value = 0 then the score is 25
if the value is -1 then the score is 0
if the value >= 365 then the score is 19
if the value is >=30 and <=365 then the score is 8
etc...
snippet of the actual process that should be improved
CASE WHEN INDICATORS.INDICATOR_NO = 'N1' THEN
(CASE
when INDICATORS.INDICATOR_VALUE =0 then 25
when INDICATORS.INDICATOR_VALUE =-1 then 0
when INDICATORS.INDICATOR_VALUE>=365 then 19
when INDICATORS.INDICATOR_VALUE>=30 and INDICATORS.INDICATOR_VALUE<=365 then 8
when INDICATORS.INDICATOR_VALUE <=29 then 0
else -100
END )
WHEN INDICATORS.INDICATOR_NO = 'N2' THEN
(CASE
when INDICATORS.INDICATOR_VALUE=-1 then 0
when INDICATORS.INDICATOR_VALUE >=1 then 16
when INDICATORS.INDICATOR_VALUE<1 then 0
else -100
END)
WHEN INDICATORS.INDICATOR_NO = 'N3' THEN
(CASE
when INDICATORS.INDICATOR_VALUE =-1 then 0
when INDICATORS.INDICATOR_VALUE >=1 then 13
when INDICATORS.INDICATOR_VALUE<1 then 0
else -100
END)
WHEN INDICATORS.INDICATOR_NO = 'N4' THEN
(CASE
when INDICATORS.INDICATOR_VALUE =-1 then 0
when INDICATORS.INDICATOR_VALUE <=1095 then 0
when INDICATORS.INDICATOR_VALUE >1095 and INDICATORS.INDICATOR_VALUE<=3650 then 3
when INDICATORS.INDICATOR_VALUE >3650 then 12
else -100
END)My idea is to create a table named L_COUNTER that will hold the counter name and unit
and create a second table L_COUNTER_RANGE thah will hold a min value and max value so i can calculate the score all the counter using a loop on MYSQL and it will be more easy and dynamic if any future counter has to be created .
Could you please suggest the DDL of the table L_COUNTER_RANGE knowing that the following structure isn't a good idea since if a range has only a >= condition then it will meet two ranges criteria istead of only 1.
L_COUNTER_RANGE (COUNTER_ID, RANGE_ID, MIN_VALUE, MAX_VALUE, SCORE)