Very Computer

This is the result I would like to have:

RowID   provid  LastDate    lastname   firstname  RecVal
------- ------- ----------- ---------  ---------- ----
201307  118498  2003-01-10             JOY NOEL   4
377532  118498  1973-12-11  NOEL       JOY        5  

RecVal says four fields are not null or not blank
for RowID 201307 and 5 for RowID 377532

Any input on directions I could go to get this value?

Thanks in advance.
B

In the first row, is lastname blank, or is it actually NULL?

If NULL, then

SELECT
    RowID, provid, LastDate, lastname, firstname, RecVal =
    CASE WHEN RowID IS NULL THEN 1 ELSE 0 END
    + CASE WHEN provid IS NULL THEN 1 ELSE 0 END
    + CASE WHEN LastDate IS NULL THEN 1 ELSE 0 END
    + CASE WHEN lastname IS NULL THEN 1 ELSE 0 END
    + CASE WHEN firstname IS NULL THEN 1 ELSE 0 END
FROM table

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Quote:> This is the result I would like to have:

> RowID   provid  LastDate    lastname   firstname  RecVal
> ------- ------- ----------- ---------  ---------- ----
> 201307  118498  2003-01-10             JOY NOEL   4
> 377532  118498  1973-12-11  NOEL       JOY        5

> RecVal says four fields are not null or not blank
> for RowID 201307 and 5 for RowID 377532

> Any input on directions I could go to get this value?

> Thanks in advance.
> B

select rowid, provid, lastdate, lastname, firstname,
    case when nullif(coalesce(rowid,''),'') is not null then 1 else 0 end +
    case when nullif(coalesce(provid,''),'') is not null then 1 else 0 end +
    case when nullif(coalesce(lastdate,''),'') is not null then 1 else 0 end
+
    case when nullif(coalesce(lastname,''),'') is not null then 1 else 0 end
+
    case when nullif(coalesce(firstname,''),'') is not null then 1 else 0
end
as RecVal
from theTable

Quote:> This is the result I would like to have:

> RowID   provid  LastDate    lastname   firstname  RecVal
> ------- ------- ----------- ---------  ---------- ----
> 201307  118498  2003-01-10             JOY NOEL   4
> 377532  118498  1973-12-11  NOEL       JOY        5

> RecVal says four fields are not null or not blank
> for RowID 201307 and 5 for RowID 377532

> Any input on directions I could go to get this value?

> Thanks in advance.
> B

Expaning the case statement has taken care of blanks and
nulls (using len() )  Thanks.  This does the trick for me.
B.