1

What would be the formula to compute a bit column from other bit columns?

For example:

CREATE TABLE #Employee
(
[empNumb]       [INT] identity(1,1) PRIMARY KEY,
[IsOk]          [BIT] NOT NULL, -- This is computed from other three columns.
[HasValidHours] [BIT] NOT NULL,
[NoDemerits]    [BIT] NOT NULL,
[NoAccidents]   [BIT] NOT NULL
);

ALTER TABLE #Employee ADD CONSTRAINT [DF_Employee_HasValidHours]  
  DEFAULT ((0)) FOR [HasValidHours];
ALTER TABLE #Employee ADD CONSTRAINT [DF_Employee_NoDemerits]  
  DEFAULT ((0)) FOR [NoDemerits];
ALTER TABLE #Employee ADD CONSTRAINT [DF_Employee_NoAccidents]  
  DEFAULT ((0)) FOR [NoAccidents];

IsOk would be computed from the and-ing of HasValidHours, NoDemerits and NoAccidents.

  • Couldn't you just run an update statement to set IsOK to 1 where the other three columns are all equal to 1? – Queue Mann Mar 11 '15 at 15:05
6

Do you mean IsOk = 1 if the other three bit columns all = 1 else IsOk = 0?

If so: -

CREATE TABLE #Employee
(
[empNumb]       [INT] identity(1,1) PRIMARY KEY,
[IsOk]          AS [HasValidHours] & [NoDemerits] & [NoAccidents],
[HasValidHours] [BIT] NOT NULL,
[NoDemerits]    [BIT] NOT NULL,
[NoAccidents]   [BIT] NOT NULL
);

See & (Bitwise AND) (Transact-SQL)

2

The expression you're looking for is (regardless of whether you do a computed column or an external UPDATE):

CASE WHEN HasValidHours = 1 AND NoDemerits = 1 AND NoAccidents = 1 THEN 1 ELSE 0 END
  • 1
    If it's for a computed column, it probably needs a cast as well. – ypercubeᵀᴹ Mar 11 '15 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.