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