CODEWITHSUNDEEP
sql-serverbinaryint
jlimited
jlimited

Reputation: 685

Function to evaluate which rows are not null and return an INT

I am trying to calculate a Numeric Value for if rows checked in SQL Server. I have done this before on the client side using .NET code. I am trying to do this within a SQL Server script as the user based came me a excel spread sheet looking like the Input Table.

Days of the Week Example

Sun = 1
Mon = 2
Tue = 4
Wed = 8
Thr = 16
Fri = 32
Sat = 64

Input Table

SYSID SUN MON TUE WED THR FRI SAT
----- --- --- --- --- --- --- ---
  1    0   0   1   0   1   0   0
  2    1   1   1   1   1   1   1
  3    0   0   0   1   0   0   1

Desired Result

SYSID DayOfWeek
----- ---------
  1      20
  2      127
  3      68

Without writing 128 Case Statements Or writing the values to another table and doing a SUM function...I am thinking a function where I pass in the values of the 7 fields and it add them together and return the value.

Is there an easier way to do this?

Upvotes: 0

Views: 46

Answers (1)

recursive
recursive

Reputation: 86064

If I understand what you are trying to do, something like this might work:

SELECT (CASE WHEN SUN=1 THEN 1  ELSE 0 END)
    +  (CASE WHEN MON=1 THEN 2  ELSE 0 END)
    +  (CASE WHEN TUE=1 THEN 4  ELSE 0 END)
    +  (CASE WHEN WED=1 THEN 8  ELSE 0 END)
    +  (CASE WHEN THU=1 THEN 16 ELSE 0 END)
    +  (CASE WHEN FRI=1 THEN 32 ELSE 0 END)
    +  (CASE WHEN SAT=1 THEN 64 ELSE 0 END)
    AS DayOfWeek
FROM InputTable

Upvotes: 1

Related Questions