How to use BIT variable for data manipulation function using SQL Server?

Can able to add two or more bit type variable in SQL Server?

this is my table

(here 0-absent 1-present, dpa-day per attendance)

masterid studentid    date     sub1 sub2 sub3 sub4  dpa
08mcaa1  08mca01  12/1/2011     o     1   1    1

based on the sub1,sub2,sub3,sub4 is added and it will be checked

the total sum=4 then dpa=1 ,

else dpa=hal开发者_开发知识库f of the day is present ,

else if the sum=0 then dpa =0

this my problem .please any one help me .

update tbl
set dpa = case (select count(*) from (
           select sub1 union all
           select sub2 union all
           select sub3 union all
           select sub4) x
           where sub1 = 1)
          when 4 then 1
          when 0 then 0
          else 0.5 end

That was just a fancy way to write

update tbl
set dpa = case 1.0+sub1+sub2+sub3+sub4
          when 4 then 1
          when 0 then 0
          else 0.5 end

You cannot add bits, so the COUNT() trick or 1.0+ is to make them countable or operable with maths.

You cannot add bits in SQL Server, but you can add a bit to a number and that returns a number, because in SQL Server a "bit" is just an integer type with valid values of 0,1.

SELECT CASE ((((0+sub1)+sub2)+sub3)+sub4) WHEN 4 THEN 1 ELSE ((((0+sub1)+sub2)+sub3)+sub4)/2 END as dpa
FROM table

An alternative, SQL Server-specific way is to explicitly cast the bit into an integer:

SELECT CASE CAST(int,sub1)+CAST(int,sub2)+CAST(int,sub3)+CAST(int,sub4)
    WHEN 4 THEN 1 ELSE (CAST(int,sub1)+CAST(int,sub2)+CAST(int,sub3)+CAST(int,sub4))/2 END AS dpa
FROM table

A more standard-based, not-SQL-Server-specific (this assumes sub1/2/3/4 are boolean types because not all database engines implement "bit" as a datatype, and integer types are trivial):

SELECT CASE
  (CASE WHEN sub1 THEN 1 ELSE 0 END)+(CASE WHEN sub2 THEN 1 ELSE 0 END)+(CASE WHEN sub3 THEN 1 ELSE 0 END)+(CASE WHEN sub4 THEN 1 ELSE 0 END)
WHEN 4 THEN 1 ELSE
  ((CASE WHEN sub1 THEN 1 ELSE 0 END)+(CASE WHEN sub2 THEN 1 ELSE 0 END)+(CASE WHEN sub3 THEN 1 ELSE 0 END)+(CASE WHEN sub4 THEN 1 ELSE 0 END))/2
END AS dpa
FROM table