3

Here's a bit of a challenge. I am working with the table ReportServer.Schedule and there is a column called DaysOfWeek. I may not be using the proper terminology here, but this is an integer from which one can derive the days of the week an SSRS subscription is set up to run. Each day is assigned a number as follows:

  • Sunday: 1
  • Monday: 2
  • Tuesday: 4
  • Wednesday: 8
  • Thursday: 16
  • Friday: 32
  • Saturday: 64

The sum of the days the subscription is set to run is the number in this column. Thus subscriptions that run Monday through Friday have a value in this column of 62.

The end result I want is a view that derives T/F flags for each day based on this number so that I have a column for each day. The method I am currently exploring to get me there is converting this integer to base-2 so then I can convert that to a varchar and parse out the days. In this example, the result would be 111110.

The final twist - I do not have the ability to create functions or stored procedures in this database, so my strong preference is to solve this within a SELECT statement...

(If push comes to shove, I will move the raw data and use a function in a separate database - and have found a number of those online.)

Improve this question

2 Answers 2

Reset to default
3

Unless I'm completely misunderstanding your question, then the below T-SQL should do it (please let me know if this isn't what you're looking for). It utilizes bitwise operators to pull out the bit masks for the days:

-- <TEST DATA>
create table DayTable
(
    id int identity(1, 1) not null,
    DayMask tinyint not null
)
go

insert into DayTable
values
(
    62
),
(
    12
)
-- </TEST DATA>

select
        case 
            when DayMask & 1 > 0
                then 1
            else 0
        end
    as Sunday,
        case 
            when DayMask & 2 > 0
                then 1
            else 0
        end
    as Monday,
        case 
            when DayMask & 4 > 0
                then 1
            else 0
        end
    as Tuesday,
        case 
            when DayMask & 8 > 0
                then 1
            else 0
        end
    as Wednesday,
        case 
            when DayMask & 16 > 0
                then 1
            else 0
        end
    as Thursday,
        case 
            when DayMask & 32 > 0
                then 1
            else 0
        end
    as Friday,
        case 
            when DayMask & 64 > 0
                then 1
            else 0
        end
    as Saturday
from DayTable
Improve this answer
2
  • 1
    that is fantastic! Does exactly what I need! I was unfamiliar with the & operator - could you explain it a bit?
    – JHFB
    May 22, 2012 at 17:34
  • 1
    @JHFB Check out this link on Bitwise Operators in SQL Server (msdn.microsoft.com/en-us/library/ms176122.aspx). Basically & does a bitwise AND comparison. So for each possible bit mask day, I AND (&) it with the decimal value of the particular bit set. If it's zero, that bit isn't set. If it's greater than zero, it is. I probably didn't explain that very well, but there are a lot of online tutorials on bit logic that'll do much better at explaining that.
    – Thomas Stringer
    May 22, 2012 at 17:47
2

Since you cannot create a function stored procedure you could join to an in memory lookup table :)

SELECT bin 
from
(
select 0 as dec, '0000000' as bin 
UNION 
select 1 as dec, '0000001' as bin 
UNION 
select 2 as dec, '0000010' as bin 
UNION 
select 3 as dec, '0000011' as bin 
UNION 
select 4 as dec, '0000100' as bin 
UNION 
select 5 as dec, '0000101' as bin 
UNION 
select 6 as dec, '0000110' as bin 
UNION 
select 7 as dec, '0000111' as bin 
UNION 
select 8 as dec, '0001000' as bin 
UNION 
select 9 as dec, '0001001' as bin 
UNION 
select 10 as dec, '0001010' as bin 
UNION 
select 11 as dec, '0001011' as bin 
UNION 
select 12 as dec, '0001100' as bin 
UNION 
select 13 as dec, '0001101' as bin 
UNION 
select 14 as dec, '0001110' as bin 
UNION 
select 15 as dec, '0001111' as bin 
UNION 
select 16 as dec, '0010000' as bin 
UNION 
select 17 as dec, '0010001' as bin 
UNION 
select 18 as dec, '0010010' as bin 
UNION 
select 19 as dec, '0010011' as bin 
UNION 
select 20 as dec, '0010100' as bin 
UNION 
select 21 as dec, '0010101' as bin 
UNION 
select 22 as dec, '0010110' as bin 
UNION 
select 23 as dec, '0010111' as bin 
UNION 
select 24 as dec, '0011000' as bin 
UNION 
select 25 as dec, '0011001' as bin 
UNION 
select 26 as dec, '0011010' as bin 
UNION 
select 27 as dec, '0011011' as bin 
UNION 
select 28 as dec, '0011100' as bin 
UNION 
select 29 as dec, '0011101' as bin 
UNION 
select 30 as dec, '0011110' as bin 
UNION 
select 31 as dec, '0011111' as bin 
UNION 
select 32 as dec, '0100000' as bin 
UNION 
select 33 as dec, '0100001' as bin 
UNION 
select 34 as dec, '0100010' as bin 
UNION 
select 35 as dec, '0100011' as bin 
UNION 
select 36 as dec, '0100100' as bin 
UNION 
select 37 as dec, '0100101' as bin 
UNION 
select 38 as dec, '0100110' as bin 
UNION 
select 39 as dec, '0100111' as bin 
UNION 
select 40 as dec, '0101000' as bin 
UNION 
select 41 as dec, '0101001' as bin 
UNION 
select 42 as dec, '0101010' as bin 
UNION 
select 43 as dec, '0101011' as bin 
UNION 
select 44 as dec, '0101100' as bin 
UNION 
select 45 as dec, '0101101' as bin 
UNION 
select 46 as dec, '0101110' as bin 
UNION 
select 47 as dec, '0101111' as bin 
UNION 
select 48 as dec, '0110000' as bin 
UNION 
select 49 as dec, '0110001' as bin 
UNION 
select 50 as dec, '0110010' as bin 
UNION 
select 51 as dec, '0110011' as bin 
UNION 
select 52 as dec, '0110100' as bin 
UNION 
select 53 as dec, '0110101' as bin 
UNION 
select 54 as dec, '0110110' as bin 
UNION 
select 55 as dec, '0110111' as bin 
UNION 
select 56 as dec, '0111000' as bin 
UNION 
select 57 as dec, '0111001' as bin 
UNION 
select 58 as dec, '0111010' as bin 
UNION 
select 59 as dec, '0111011' as bin 
UNION 
select 60 as dec, '0111100' as bin 
UNION 
select 61 as dec, '0111101' as bin 
UNION 
select 62 as dec, '0111110' as bin 
UNION 
select 63 as dec, '0111111' as bin 
UNION 
select 64 as dec, '1000000' as bin 
UNION 
select 65 as dec, '1000001' as bin 
UNION 
select 66 as dec, '1000010' as bin 
UNION 
select 67 as dec, '1000011' as bin 
UNION 
select 68 as dec, '1000100' as bin 
UNION 
select 69 as dec, '1000101' as bin 
UNION 
select 70 as dec, '1000110' as bin 
UNION 
select 71 as dec, '1000111' as bin 
UNION 
select 72 as dec, '1001000' as bin 
UNION 
select 73 as dec, '1001001' as bin 
UNION 
select 74 as dec, '1001010' as bin 
UNION 
select 75 as dec, '1001011' as bin 
UNION 
select 76 as dec, '1001100' as bin 
UNION 
select 77 as dec, '1001101' as bin 
UNION 
select 78 as dec, '1001110' as bin 
UNION 
select 79 as dec, '1001111' as bin 
UNION 
select 80 as dec, '1010000' as bin 
UNION 
select 81 as dec, '1010001' as bin 
UNION 
select 82 as dec, '1010010' as bin 
UNION 
select 83 as dec, '1010011' as bin 
UNION 
select 84 as dec, '1010100' as bin 
UNION 
select 85 as dec, '1010101' as bin 
UNION 
select 86 as dec, '1010110' as bin 
UNION 
select 87 as dec, '1010111' as bin 
UNION 
select 88 as dec, '1011000' as bin 
UNION 
select 89 as dec, '1011001' as bin 
UNION 
select 90 as dec, '1011010' as bin 
UNION 
select 91 as dec, '1011011' as bin 
UNION 
select 92 as dec, '1011100' as bin 
UNION 
select 93 as dec, '1011101' as bin 
UNION 
select 94 as dec, '1011110' as bin 
UNION 
select 95 as dec, '1011111' as bin 
UNION 
select 96 as dec, '1100000' as bin 
UNION 
select 97 as dec, '1100001' as bin 
UNION 
select 98 as dec, '1100010' as bin 
UNION 
select 99 as dec, '1100011' as bin 
UNION 
select 100 as dec, '1100100' as bin 
UNION 
select 101 as dec, '1100101' as bin 
UNION 
select 102 as dec, '1100110' as bin 
UNION 
select 103 as dec, '1100111' as bin 
UNION 
select 104 as dec, '1101000' as bin 
UNION 
select 105 as dec, '1101001' as bin 
UNION 
select 106 as dec, '1101010' as bin 
UNION 
select 107 as dec, '1101011' as bin 
UNION 
select 108 as dec, '1101100' as bin 
UNION 
select 109 as dec, '1101101' as bin 
UNION 
select 110 as dec, '1101110' as bin 
UNION 
select 111 as dec, '1101111' as bin 
UNION 
select 112 as dec, '1110000' as bin 
UNION 
select 113 as dec, '1110001' as bin 
UNION 
select 114 as dec, '1110010' as bin 
UNION 
select 115 as dec, '1110011' as bin 
UNION 
select 116 as dec, '1110100' as bin 
UNION 
select 117 as dec, '1110101' as bin 
UNION 
select 118 as dec, '1110110' as bin 
UNION 
select 119 as dec, '1110111' as bin 
UNION 
select 120 as dec, '1111000' as bin 
UNION 
select 121 as dec, '1111001' as bin 
UNION 
select 122 as dec, '1111010' as bin 
UNION 
select 123 as dec, '1111011' as bin 
UNION 
select 124 as dec, '1111100' as bin 
UNION 
select 125 as dec, '1111101' as bin 
UNION 
select 126 as dec, '1111110' as bin 
UNION 
select 127 as dec, '1111111' as bin 
) AS dummy
WHERE dec = 62
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.