CODEWITHSUNDEEP
sqlsql-server
shorton
shorton

Reputation: 353

count items in a row conditionally

Want to count the number of items in a row that meet a list of criteria and place result in row's errorcnt column. The actual data has abotu 15 conditions and a hundred columns. I think this sample should illustrate what I'm after.

CREATE TABLE #MyList
(
    Item1   NVARCHAR(100),
    Item2   NVARCHAR(100),
    date1   DATE,
    Date2   date,
    errorcnt int
)

INSERT INTO #MyList
  (
    Item1,
    Item2,
    Date1,
    Date2
  )
VALUES
('Dog','Puppy',   '2020-01-01', '2030-01-01'),
('Cat', 'Kitten', '2020-02-02', '2020-03-03')

Sample conditions to count:

When Item1 <> Dog, OR 
when Item2 <> puppy, OR 
when Date2>date1

Each of those are to be counted so for record #2, 

Item1 <> Dog = 1
Item2 <> puppy = 1
Date 2 > Date 1 = 1
Total 3 errors.

Output for row 2 would be:

Cat, Kitten, 2020-02-02, 2020-03-03, 3

Novice, can't figure out how to do it best.

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Use case expressions:

select ml.*,
       ( (case when Item1 <> 'Dog' then 1 else 0 end) + 
         (case when when Item2 <> 'puppy' then 1 else 0 end) +
         (case when Date2 > date1 then 1 else 0 end)
       ) as total_errors
from #mylist ml;

Upvotes: 6

Related Questions