Computed Column Bit based on other Bit Columns

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

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
);
``````

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
``````
• If it's for a computed column, it probably needs a cast as well. – ypercubeᵀᴹ Mar 11 '15 at 20:54