In SQL Server 2012, I have a set of columns that are defined as varchar. The values are thresholds for a metric and can be either numbers or string values. For example, the thresholds for temperature are numbers 0 and 100, whereas, thresholds for door would be "open" "closed".
I want to test a value [ResponseValue] to see if it's in between two define threshold values [Value_Threshold_min] and [Value_Threshold_max]. The [ResponseValue] can be either numeric or varchar but always stored as varchar. The Threshold_max/min are stored as varchar.
, (CASE WHEN ResponseValue BETWEEN steps.value_threshold_min AND steps.Value_Threshold_max else END THEN column_color
JOIN responses on responses.stepid = steps.id
WHERE stepid = 4447
CASE WHEN isnumeric([Responses].[ResponseValue]) = 1
THEN CAST([Responses].[ResponseValue] as int)
ELSE [Responses].[ResponseValue] END)
BETWEEN steps.value_threshold_min AND steps.Value_Threshold_max THEN column _color
SELECT responsevalue, CASE WHEN TRY_CONVERT(INT,ResponseValue) BETWEEN TRY_CONVERT(INT,steps.value_threshold_min) AND TRY_CONVERT(INT,steps.Value_Threshold_max) THEN 'Int Color' WHEN ResponseValue BETWEEN steps.value_threshold_min AND steps.Value_Threshold_max THEN 'VarcharColor' ELSE NULL END as column_color FROM steps JOIN responses on responses.stepid = steps.id WHERE stepid = 4447