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.

Its not clear why you example turns into those 5 rows, can u expand on that? — sagi, Feb 03 '16 at 09:36
Of course, I can... DayOfWeek = 62 This represents 2 + 4 + 8 + 16 + 32 = 62, respectively: monday + tuesday + wednesday + thursday + fryday. So that single row represents 5 days of the week the same task will be run. — navossoc, Feb 03 '16 at 09:38
Are these all values you have in your table? Or you can have other values as well? — Haytem BrB, Feb 03 '16 at 09:39
My DayOfWeek column is limited to 2^7 (0 ~ 127), being 0 = none and 127 all days (whole week) — navossoc, Feb 03 '16 at 09:43

David Rushton · Accepted Answer · 2016-02-03T10:36:05.380

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.

Yes, that is it... I just need to make a little tweak to exclude the rows that represents more than one day (62 and 127). WHERE s2.DayId IN (1, 2, 4, 8, 16, 32, 64) — navossoc, Feb 03 '16 at 10:53
Do you think it will be easier to apply this same idea if I have multiple columns (one for each day) like you said? — navossoc, Feb 03 '16 at 10:57
Ah yes I forgot 62 and 127 would be returned! It depends where your logic lives. If the hard work is happening in a frontend then maybe not. But if you are doing all the heavy lifting in SQL then yes, I'd combine with bit fields for simpler filtering. Something like *WHERE IsMonday = 1* is easier to read/write/follow than *Day & Day = 1*. — David Rushton, Feb 03 '16 at 11:01

Expand bit fields to multiple rows (kinda join)

1 Answers1