Bitwise Aggregreates

  • I have a small issue. I'm considering using bits to store status data, but need an Aggregrate for bitwise operations.

    See topic are posted at http://www.sqlservercentral.com/columnists/dpoole/usingbitstostoredata.asp

      I have jobs with workphases and need a bitwise OR() and XOR() aggregate function for the Status field.

    Example:  Select JobID, OR(Status)

                  From Jobs join Workphases on Jobs.JobID = Workphases.JobID

                  Group by JobID

    JobID    Status

     1         00001 (2)

     1         00010 (4)

     1         00001 (2)

    Should return 00011 (6) not 00100 (8)

     

     

  • Seems that what you want is to get rid of the duplicate(s) jobid + status before the aggregate..?

    What about first selecting the distinct JobID + Status, then aggregating that result?

    Select o.JobID, OR(i.Status)

    From (

    Select JobID, Status

    From Jobs join Workphases on Jobs.JobID = Workphases.JobID

    Group by JobID, Status

    ) i

    join Jobs o on o.JobID = i.JobID

    Should give you

    JobID Status

    1 00001 (2)

    1 00010 (4)

    in the inner query..

    /Kenneth

  • I see what you mean, get rid of the duplicates and then I can use the SUM() for the aggregate.

    Select o.JobID, SUM(i.Status)

    From (

    Select JobID, Status

    From Jobs join Workphases on Jobs.JobID = Workphases.JobID

    Group by JobID, Status

    ) i

    join Jobs o on o.JobID = i.JobID

    Thanks

  • Yup, that was the general idea.

    /Kenneth

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply