Use CASE syntax to test rule?

• kkbear

  SSC-Addicted

  Points: 457

  More actions

  August 14, 2017 at 7:52 pm

  #339315

  I have three fields with values in my table, account_number, income, and income_level. I need to test if the income_level is assigned correctly. The rule is if income>50,000, income_level=hight, 30,000-50,000 medium, <30,000 low. I need to returns account nbr that has incorrect income_level. How can I use CASE sentence to implement it?

  account_nbr    income     income_level
  1                      100,000    high
  2                      2,000        low
  3                      15,000      medium

• Thom A

  SSC Guru

  Points: 99130

  More actions

  August 15, 2017 at 2:54 am

  #1955254

  This doesn't sound like you need a CASE statement. As you only want to return results which don't conform then you just need to use a WHERE clause:
  CREATE TABLE #Sample
  (Account_br int IDENTITY(1,1),
  Income int,
  Income_Level varchar(10));
GO

  INSERT INTO #Sample (Income, Income_Level)
VALUES
  (100000,'high'),
  (2000,'low'),
  (15000,'medium');
GO

  SELECT *
FROM #Sample;
GO

  SELECT *
FROM #Sample
WHERE (Income > 50000 AND Income_Level != 'high')
   OR (Income BETWEEN 30000 AND 50000 AND Income_Level != 'medium')
   OR (Income < 30000 AND Income_Level != 'low');
GO

  DROP TABLE #Sample;
GO

  If, however, this is always going to be the required value, why not use a computed column, or add a constraint on the table to stop records having the incorrect data?
  

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk