Expand bit fields to multiple rows (kinda join)

Question

let's say I have a table like this:

CREATE TABLE [dbo].[Scheduler](
    [DayOfWeek] [tinyint] NOT NULL,
    [Time] [time](0) NOT NULL,
    [Action] [varchar](255) NOT NULL
)

And some data, like this:

INSERT INTO Scheduler VALUES (1, '11:00:00', 'Sunday')
INSERT INTO Scheduler VALUES (2, '12:00:00', 'Monday')
INSERT INTO Scheduler VALUES (4, '13:00:00', 'Tuesday')
INSERT INTO Scheduler VALUES (8, '14:00:00', 'Wednesday')
INSERT INTO Scheduler VALUES (16, '15:00:00', 'Thursday')
INSERT INTO Scheduler VALUES (32, '16:00:00', 'Friday')
INSERT INTO Scheduler VALUES (64, '17:00:00', 'Saturday')
INSERT INTO Scheduler VALUES (62, '06:00:00', 'Every business day')
INSERT INTO Scheduler VALUES (127, '08:00:00', 'Every day')

How can I produce multiple rows in a SELECT statement if DayOfWeek has more than one flag?

For example, this row:

INSERT INTO Scheduler VALUES (62, '06:00:00', 'Every business day')

It will be represented in 5 rows in a SELECT statement (one for each day/flag set)

DayOfWeek Time             Message
--------- ---------------- ---------------------------
2         06:00:00         Every business day
4         06:00:00         Every business day
8         06:00:00         Every business day
16        06:00:00         Every business day
32        06:00:00         Every business day

Running the same query with all the data will give me 19 rows.

• 7 rows - one row for each individual day (1, 2, 4, 8, 16, 32, 64)
• 5 rows - business days (62)
• 7 rows - every day (127)

I'm not sure how can I do this. I think I can use a cursor to do this, but it is the best option here?

Thanks.

Answer 1

You need SQL Server's Bitwise Operators. This example shows how you can determine which flags are contained in the current value.

DECLARE @Mon        INT = 1;
DECLARE @Tue        INT = 2;
DECLARE @Wed        INT = 4;
DECLARE @MonAndTue  INT = 3;    -- Mon (1) and Tue (2) = 3.

SELECT
    @MonAndTue & @Mon,      -- Contains Monday, returns Monday.
    @MonAndTue & @Tue,      -- Contains Tuesday, returns Tuesday.
    @MonAndTue & @Wed       -- Does not contain Wednesday, returns 0.

Where possible I'd recommend avoiding bit masking based solutions in SQL Server. These work great in other languages (C springs to mind). But SQL works best when each column holds a single value, describing a single item. Of course you could combined these approaches. This table design allows you to retain the base 2 keys (great for the frontend) and includes simple bit fields that make filtering in the backend a straightforward task.

CREATE TABLE [Day]
(
    DayId       INT PRIMARY KEY,        
    [DayName]   VARCHAR(9),
    IsMonday    BIT,
    IsTuesday   BIT,
    ...
    IsSunday    BIT
)
;

EDIT

Sorry! I didn't actually answer your question. To use the bitwise operations in a join your need syntax long these lines:

WITH Scheduler AS
(
    /* Sample data.
     */
    SELECT          
        *
    FROM
        (
            VALUES  
                (1, 'Sunday'),
                (2, 'Monday'),
                (4, 'Tuesday'),
                (8, 'Wednesday'),
                (16, 'Thursday'),
                (32, 'Friday'),
                (64, 'Saturday'),
                (62, 'Every business day'),
                (127, 'Every day')
        ) AS r(DayId, [DayName])
)
/* This join returns every day from s2 that 
 * is contained within the s1 record Every business day.
 */
SELECT
    *
FROM
    Scheduler AS s1
        INNER JOIN Scheduler AS s2          ON (s1.DayId & s2.DayId) =     s2.DayId
WHERE
    s1.DayId = 62
;

Here S1 is filtered to return Every business day. S2 joins on S1 where a match is found. This returns Mon, Tue, Wed, etc without returning Sat and Sun.