Checking to see if today is in the scheduled day utilizing a binary mapping table in SQL

Question

I am pulling XML data from a 3rd party application using SQL Server 2008 (which I only have read-only access to the DB) and it stores the day or days of the week a job is supposed to run in one of those XML fields.

SQL uses a recursive day code of Sunday=1, Monday=2, Tuesday=4, Wednesday=8, Thursday=16, Friday=32, Saturday=64.

I am pulling the day from this XML field like the following.

case 
   when (job.SJDefn.value('(schedules/schedule/name)[1]', 'varchar(30)') ) like '%Week%' 
      then job.SJDefn.value('(/schedules/schedule/recurring/date/week/day_of_week)[1]', 'int') 
   else 0 
end as JDOW,

I was originally utilizing the this to determine the current date of the week as but the values were obviously not compatible.

DATEPART(dw, getdate()) AS CDOW, -- Sun 1 Mon 2 Tue 3 Wed 4 Thu 5 Fri 6 Sat 7`

So I moved to:

case DATEPART(dw, getdate())
   when 1 then 1        -- Sunday (1=1)
   when 2 then 2        -- Monday (2=2)
   when 3 then 4        -- Tueday (3=4
   when 4 then 8        -- Wednesday (4=8)
   when 5 then 16       -- Thursday (5=16)
   when 6 then 32       -- Friday (6=32)
   when 7 then 64       -- Saturday (7=64)
   else NULL 
end as CDOW,

The challenge is that it is an easy translation when the job just runs one day of a week.. but what about Monday/Wednesday/Friday well that is 42 which is an aggregate of the days Monday (2) + Wednesdays (8) + Friday (32).

I could translate this to text like in this question and do a string compare to a temp table but that seems inefficient.

I know that there is a table that can be built like this code to build a comparison table and I've checked the SQL Server Agent documentation (which this isn't but it is quite similar.

It seems like all possible combinations for Sunday - Saturday are basically a bitmap ranged from 1-127.. such as 42 = 0010 1010 which could be an on/off values for each day (first position always 0, 127 = 0111 111) and with that.

Position 1 = Always 0    ; binary 0000 0000
Position 2 = Sunday 1    ; binary 0000 0001
Position 3 = Monday 2    ; binary 0000 0010
Position 4 = Tueday 4    ; binary 0000 0100
Position 5 = Wednesday 8 ; binary 0000 1000
Position 6 = Thursday 16 ; binary 0001 0000
Position 7 = Friday 32   ; binary 0010 0000
Position 8 = Saturday 64 ; binary 0100 0000

I am thinking about how to potentially use a bitwise & operator but it compares the entire bit for an exact match and not a single position as I understand it so not thinking it will accomplish exactly what I want.

What I want is if the current day is in the schedule I get a true / false result from the comparison. I don’t care about interpreting the values into plain English.. For example if the string was 0011 1110 (Monday - Friday), then if the current day value is the equivalent of 0000 0010 (Monday) I be get true. If it was Sunday (0000 0001) it would be false if the reference was that 0010 1110.

I would really think there is a much simpler way of simply checking if the current day falls into the configuration in a few lines vs. building a temporary table to compare against.

So my question: given the information above, is there a simple function / query that I can execute to compare the two and return a boolean result (0/1 or Y/N) if the the current day matches the schedule?

Answer 1

This will give you a 1/0 result for the current date given a schedule bitmap:

declare @Today as Date = GetDate();
-- Assuming that   @@DateFirst   is correctly set:
declare @DoW as Int = DatePart( weekday, @Today );
-- Shift a bit to the appropriate position.
declare @Mask as Int = Power( 2, @DoW - 1 );

-- Sample schedule bitmap.
declare @Schedule as Int = 42;
-- Sign   is used to convert the result of the bitwise-and to a 0 or 1.
--   Any positive value, indicating the corresponding bit is set, will return 1.
--   If there is no match, the result will be zero.
select @Today as Today, @DoW as Dow, @Mask as Mask, @Schedule as Schedule,
  Sign( @Schedule & @Mask ) as IsScheduledToday;

As a professor once said, "you're always off by one in this business." It may need a tweak, but should be close.

Answer 2

It's a little difficult to tell exactly what you're trying to do here. You've explained the problem, but didn't provide any desired output. Since you have read-only access, however, I assume you're only interested in querying the data.

However, if you have an integer like 42, and you want to test bitwise if the bit for Monday is set (that is, 2's place), then you do this:

42 & 2 = 2

If you want to display days of the week you could do something like:

SELECT
    CASE WHEN JDOW & 1  = 1  THEN 'U' ELSE '' END
    + CASE WHEN JDOW & 2  = 2  THEN 'M' ELSE '' END
    + CASE WHEN JDOW & 4  = 4  THEN 'T' ELSE '' END
    + CASE WHEN JDOW & 8  = 8  THEN 'W' ELSE '' END
    + CASE WHEN JDOW & 16 = 16 THEN 'R' ELSE '' END
    + CASE WHEN JDOW & 32 = 32 THEN 'F' ELSE '' END
    + CASE WHEN JDOW & 64 = 64 THEN 'S' ELSE '' END AS scheduled_days
FROM (VALUES (42),(84),(96), (4)) UnnamedTable (JDOW)

If you want you could create another table:

CREATE TABLE BitwiseWeekDay (
    code tinyint primary key not null,
    day_name nvarchar(10) not null,
    day_short_name nvarchar(4) not null,
    day_code nvarchar(1) not null
)

INSERT INTO BitwiseWeekDay VALUES
(1,'Sunday','Sun','U'),
(2,'Monday','Mon','M'),
(4,'Tuesday','Tue','T'),
(8,'Wednesday','Wed','W'),
(16,'Thurday','Thur','R'),
(32,'Friday','Fri','F'),
(64,'Saturday','Sat','U')

SELECT u.JDOW, 
    b.code,
    b.day_name
FROM (VALUES (42),(84),(96), (4)) u (JDOW) 
    INNER JOIN BitwiseWeekDay b
        ON  u.JDOW & b.code = b.code
 ORDER BY u.JDOW, b.code

But, I don't really know what you're looking for.