SQL Server Programming - Adding bit fields

Author

Adding bit fields

David C

2005-02-28, 4:02 pm

I tried to add 7 bit filelds together but it failed. I have 7 bit fields to
indicate days of w

that person works, e.g. Mon, Tue,.....Sun and want to
add them together to come up with # days worked in a w

. I was trying
[Mon]+[Tue]+[Wed].......+[Sun] but it failed. Is there any easier way than
multiple IF statements? Thanks.

David

Michael C#

2005-02-28, 4:02 pm

Try CAST([Mon] AS INTEGER) + CAST([Tue] AS INTEGER) + ...

Are you sure you want to use a bit field there? Personally I find they're
more trouble than they're worth.

Thx,
Mike C.

"David C" <dlchase@lifetimeinc.com> wrote in message
news:OHNnCXcHFHA.2620@tk2msftngp13.phx.gbl...
>I tried to add 7 bit filelds together but it failed. I have 7 bit fields
>to indicate days of w

that person works, e.g. Mon, Tue,.....Sun and want
>to add them together to come up with # days worked in a w

. I was trying
>[Mon]+[Tue]+[Wed].......+[Sun] but it failed. Is there any easier way than
>multiple IF statements? Thanks.
>
> David
>

David Portas

2005-02-28, 4:02 pm

How about a change of design. Much easier to write queries against
something like this:

CREATE TABLE WorkingTime (ssn VARCHAR(10) NOT NULL REFERENCES Employees
(ssn), work_dt DATETIME NOT NULL, PRIMARY KEY (ssn,work_dt))

For example:

SELECT ssn, COUNT(*)
FROM Timesheet
WHERE work_dt >= '20050228'
AND work_dt < '20050307'
GROUP BY ssn

As you are discovering, repeating groups of BIT columns create all
sorts of problems. It is a cardinal error to represent data in column
names.

If you can't change it then use CASE:

SELECT
CASE mon WHEN 1 THEN 1 ELSE 0 END +
CASE tue WHEN 1 THEN 1 ELSE 0 END +
CASE wed WHEN 1 THEN 1 ELSE 0 END +
...

--
David Portas
SQL Server MVP
--

David

2005-02-28, 9:01 pm

I do want the bit fields because they work great with checkboxes in
Access forms.

David

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

David Portas

2005-02-28, 9:01 pm

That doesn't make them a good design from a database perspective. You
probably only have to design one Access form but might have to develop
many queries against the data so it's more important to have a logical
design that works well for data manipulation and reporting.

--
David Portas
SQL Server MVP
--

David

2005-02-28, 9:01 pm

You may be right. This was an uploaded Access database and in Access
the reporting and querying worked fine. Now that it is on SQL maybe we
will change it. It just seemed odd that I could total up yes/no field
in Access, but not on something as powerful as SQL Server.

David

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Michael C#

2005-02-28, 9:01 pm

Hi David,

SQL tends to make you re-think things the 'right' way. For instance, Access
might have let you total Yes/No fields, but that concept really doesn't make
much sense. After all, what is YES + YES + YES equal to? An "Emphatic
YES!!"? In SQL you would most likely use a COUNT() function in a SELECT
statement to determine how many YES's you had, which is what it sounds like
you're really after here. If you want to stay with the bits, David Portas
gave you the CASE statement that will do the trick, or you can do the CAST
conversion; I would recommend normalizing your DB and getting away from bits
though.

Thanks,
Mike C.

"David" <daman@biteme.com> wrote in message
news:e96y3TeHFHA.3484@TK2MSFTNGP12.phx.gbl...
> You may be right. This was an uploaded Access database and in Access
> the reporting and querying worked fine. Now that it is on SQL maybe we
> will change it. It just seemed odd that I could total up yes/no field
> in Access, but not on something as powerful as SQL Server.
>
> David
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Sponsored Links