Zafar Iqbal

Recently I was working on a procedure which required that I marked some items in different categories, and then based on that marking we have to summarize items in different sections. I have extracted the main idea from this procedure for our example. It is using some Boolean algebra to segregate items into different sections.

First lets create our test table.

CREATE TABLE #XYZ
(
    Id            INT,
    IsBoard       BIT,
    IsAdjustment  BIT,
    IsCredit      BIT,
    IsWithout     BIT
)

Then populate this table with some test data.

INSERT INTO #XYZ VALUES (1, 1, 0, 0, 0)
INSERT INTO #XYZ VALUES (2, 1, 0, 0, 0)
INSERT INTO #XYZ VALUES (3, 1, 0, 0, 1)
INSERT INTO #XYZ VALUES (4, 1, 0, 1, 0)
INSERT INTO #XYZ VALUES (5, 1, 0, 0, 0)
INSERT INTO #XYZ VALUES (6, 1, 1, 1, 0)
INSERT INTO #XYZ VALUES (7, 1, 1, 0, 0)
INSERT INTO #XYZ VALUES (8, 1, 0, 0, 1)
INSERT INTO #XYZ VALUES (9, 1, 1, 0, 0)

Now comes the actual segregation logic. For this define a variable @Type, and check the results by assigning values between 1-4.

DECLARE @Type INT
SET @Type = 4

SELECT *
FROM   #XYZ
WHERE  (CASE
            WHEN @Type = 1 THEN (IsBoard | IsAdjustment)
            WHEN @Type = 2 THEN IsCredit
            WHEN @Type = 3 THEN IsWithout
            WHEN @Type = 4 THEN (IsWithout ^ 1) & amp; (IsCredit ^ 1)
            ELSE 0
        END) = 1

At the end clean up your test tables as usual.

DROP TABLE #XYZ