CODEWITHSUNDEEP
sqlsql-server
etm124
etm124

Reputation: 2140

Counting the number of successful bitwise matches

I have a value stored in a table with the results of a multiple choice answer. The choices look similar to this:

Allergy 1
Cardiology  2
Chest Disease   4
Dermatology 8
Dialysis    16
Emergency Room  32
Ambulance Trips 64
Gastroenterology    128
General Medicine    256
General Surgery 512
Gynecology  1024
Hematology  2048
Methadone   4096
Neurology   8192
Neurosurgery    16384
Obstetrics  32768
Ophthalmology   65536
Orthopedics 131072
Physical Therapy    262144
Plastic Surgery 524288
Podiatry    1048576
Proctology  2097152
Psychiatry  4194304
Surgery Performed   8388608
Thoracic Surgery    16777216
Urology 33554432
Outside X-Rays  67108864
Diagnostic Test(outside)    134217728
Other Trips/Outside Consults    268435456

When someone fills out the answer, the answer/value is stored something like below:

id                   form_record form_item   value                entered_date
-------------------- ----------- ----------- -------------------- -----------------------
376418               17453       2943        1114241              2013-05-23 09:56:06.000
376889               17475       2943        1056896              2013-05-23 11:00:06.000

So when I try to see if the what users selected, for example, Neurology, today I do something like this:

select
    fa.value & 8192 as 'Neurology'
from
    form_records fr
        inner join form_names fn on fn.id = fr.form_Name
        inner join form_items fi on fn.id = fi.form_name
        inner join form_answers fa on fr.id = fa.form_record
            and fa.form_item = fi.id
where
    fn.name like '%Consultation Review Form%'   
    and dateadd(dd, 0, datediff(dd, 0, fr.date)) = dateadd(dd, 0, datediff(dd, 0, getdate()))

My results are this:

Neurology
--------------------
0
8192

(2 row(s) affected)

I know that the seconduser that completed this answer, selected Neurology, but the first user that completed this answer, did not select Neurology.

What am trying to do is get a count for the day of the number of Neurology answers/values.

By doing this:

select
    count(fa.value & 8192) as 'Neurology'

Returns 2. I'd like it to return 1.

Upvotes: 1

Views: 153

Answers (1)

gbn
gbn

Reputation: 432742

select
    count(NULLIF(fa.value & 1, 0)) as 'Neurology'
    ...

This changes "no match" to NULL and COUNT will ignore NULLs.

Upvotes: 3

Related Questions