Início / dba / Perguntas / 18277
Accepted
JHFB
Asked: 2012-05-23 09:10:53 +0800 CST

posso converter um inteiro para base-2 sem uma função

  • 772

Aqui está um pouco de um desafio. Estou trabalhando com a tabela ReportServer.Schedulee existe uma coluna chamada DaysOfWeek. Posso não estar usando a terminologia adequada aqui, mas este é um número inteiro do qual se pode derivar os dias da semana em que uma assinatura do SSRS está configurada para ser executada. A cada dia é atribuído um número da seguinte forma:

  • Domingo: 1
  • segunda-feira: 2
  • Terça-feira: 4
  • Quarta-feira: 8
  • Quinta-feira: 16
  • Sexta-feira: 32
  • sábado: 64

A soma dos dias em que a assinatura está definida para execução é o número nesta coluna. Assim, as assinaturas executadas de segunda a sexta-feira têm um valor nesta coluna de 62.

O resultado final que desejo é uma exibição que deriva sinalizadores T/F para cada dia com base nesse número, para que eu tenha uma coluna para cada dia. O método que estou explorando atualmente para chegar lá é converter esse número inteiro em base 2 para que eu possa convertê-lo em um varchar e analisar os dias. Neste exemplo, o resultado seria 111110.

A reviravolta final - não tenho a capacidade de criar funções ou procedimentos armazenados neste banco de dados, portanto, minha forte preferência é resolver isso em uma SELECTinstrução ...

(Se o push for forçado, moverei os dados brutos e usarei uma função em um banco de dados separado - e encontrei vários deles online.)

sql-server sql-server-2008

2 respostas

  1. Best Answer

    A menos que eu esteja entendendo mal sua pergunta, o T-SQL abaixo deve fazer isso (informe-me se não é isso que você está procurando). Ele utiliza operadores bit a bit para extrair as máscaras de bits dos dias:

    -- <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
    • 3

  2. Como você não pode criar um procedimento armazenado de função, você pode ingressar em uma tabela de pesquisa na memória :)

    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
    • 2

relate perguntas