none
Ajuda RRS feed

  • Pergunta

  • Pessoal, Boa tarde.

    Estou quebrado a cabeça, porem nao consigo achar uma solução. Tenho uma tabela que registra o ID do operador, Numero da Ordem de servico, data de inicio, hr de inicio, dt termino e hora de termino. 

    Nota que no resultado, ele repete o nome do operador de acordo com a data que ele realizou um trabalho.

    Gostaria de Fixar os nomes dos operadores e abrir como se fosse um calendario do perido que o usuario poderá escolher nos campos de Data de Inicio e data de Termino conforme exemplo abaixo:

    Resumindo, trazer fixo na primeira coluna o nome dos operadores mesmo que não tenham executado nenhum trabalho e as demais colunas serem as datas que o usuario selecionou para pesquisa.  os X seriam as marcações dos dias que o operador foi escalado para o trabalho.

    A tabela que puxo os dados possui os seguintes campos: 

    ID_OS,  ID_OPERADOR,  DT_INICIO, HR_INICIO,  DT_TERMINO, HR_TERMINO

    Se puderem me dar uma força eu agradeço, realmente to quebrando a cabeça com isso.

    Obrigado

    Paulo

    quinta-feira, 5 de setembro de 2019 20:39

Todas as Respostas

  • Paulo, e onde se obtém o nome do operador?

    Sobre "as datas que o usuario selecionou para pesquisa", são datas individuais ou é um período?

    Você pode postar amostra dos dados? No caso, publicar o código SQL de criação das tabelas bem como o código SQL de carga dos dados para testes.

    Qual é o leiaute desejado para o resultado da consulta?

    Há limite no número de colunas contendo data?

    ---

    Considerando-se "trazer fixo na primeira coluna o nome dos operadores mesmo que não tenham executado nenhum trabalho e as demais colunas serem as datas que o usuario selecionou para pesquisa.  os X seriam as marcações dos dias que o operador foi escalado para o trabalho", me parece que a solução envolve o uso de pivotamento. Além disso, como a identificação das colunas é variável, terá que ser pivô dinâmico. Sugiro a leitura do artigo “Alas & Pivôs” caso não tenha domínio sobre essas técnicas.

    Considerando-se um período estático de 1/1/2012 a 5/1/2012, eis uma sugestão de código SQL:

    -- código #1
    declare @Data_inicial date, @Data_final date;
    set @Data_inicial= convert (date, '1/1/2012', 103);
    set @Data_final= convert (date, '5/1/2012', 103);
    
    with 
    -- gera as datas do período
    Datas as ( SELECT @Data_inicial as Dia union all SELECT dateadd (day, +1, Dia) from Datas where Dia < @Data_final ), -- gera mapa de marcações para o período
    Marcacoes as ( SELECT O.NOME_OPERADOR, D.Dia, case when M.ID_OPERADOR is null then '' else 'X' end as Escalado from tbOperador as O cross join Datas as D left join tbMarcacao as M on M.ID_OPERADOR = O.ID_OPERADOR and D.Dia between M.DT_INICIO and M.DT_TERMINO )
    -- SELECT NOME_OPERADOR, [20120101], [20120102], [20120103], [20120104], [20120105] from Marcacoes pivot (max (Escalado) for Dia in ([20120101], [20120102], [20120103], [20120104], [20120105])) as P;

    Utilizando massa de dados própria, eis como ficou o relatório:

     

    Lembre-se de marcar esta resposta se ela te ajudou a resolver o problema.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz sexta-feira, 6 de setembro de 2019 16:24
    • Sugerido como Resposta José Diz sexta-feira, 13 de setembro de 2019 14:11
    quinta-feira, 5 de setembro de 2019 21:26
  • José, Bom dia. 

    Obrigado por responder, então ontem não consegui postar as imagens de como deveria ficar pq minha conta não estava  validada, e ainda nao permite que eu adicone imagem

    O nome do operador vem da tabela funcionario, aonde farei um join.

    As datas de pesquisa, o usuario pode selecionar qualquer data de inicio e qualquer data de termino, por exemplo, ele pode selecionar de 01/01/2019 a 31/04/2019 ou de 01/01/2019 a 20/01/2019 , ai vai do periodo que ele desejar.

    O exemplo que vc postou com o resultado, é exatamente isso que preciso porem não por mês, mais pelos dias que ele esta escalado no periodo que o gestor selecionou no relatorio.

    é exatamente isso.....

    sexta-feira, 6 de setembro de 2019 12:33
  • As datas de pesquisa, o usuario pode selecionar qualquer data de inicio e qualquer data de termino, por exemplo, ele pode selecionar de 01/01/2019 a 31/04/2019 ou de 01/01/2019 a 20/01/2019 , ai vai do periodo que ele desejar.

    Mas um período de janeiro a abril envolve a criação de cerca de 120 colunas! Me parece que isso se torna impraticável de visualizar.

    De qualquer forma para resolver o que solicita é necessário o uso do pivotamento dinâmico, pois tanto o número de colunas quanto a denominação das mesmas são variáveis. Neste caso recomendo a leitura do artigo “Alas & Pivôs” para transformar o código #1 de pivô em pivô dinâmico.

    No exemplo que postei o resultado não está por mês mas sim no período de 1/1/2012 a 5/1/2012.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz sexta-feira, 6 de setembro de 2019 14:21
    sexta-feira, 6 de setembro de 2019 14:21
  • Na realidade estou pensando em deixar fixo de 1 a 31 no caso dentro do proprio mes mesmo, assim fica mais facil.

    Obrigado pela dica e vou estudar sobre. 

    Um grande abraço!

    sexta-feira, 6 de setembro de 2019 14:25
  • Na realidade estou pensando em deixar fixo de 1 a 31 no caso dentro do proprio mes mesmo, assim fica mais facil.
    Obrigado pela dica e vou estudar sobre. 

    A ideia de restringir o número de dias é boa, mas não é necessário que fique restrita a um mesmo mês. De qualquer forma, você é quem conhece a aplicação e sabe o que pode ou não ser feito.

    Eis a conversão do código #1 para pivô dinâmico, seguindo o passo a passo que está no artigo “Alas & Pivôs”:

    -- código #2 v3
    
    --
    IF Object_ID ('tempDB..#Datas', 'U') is not null
    DROP TABLE #Datas;

    --
    declare @Data_inicial date, @Data_final date; set @Data_inicial= convert (date, '10/1/2012', 103); set @Data_final= convert (date, '15/1/2012', 103); -- gera as datas do período em tabela temporária with Periodo as ( SELECT @Data_inicial as Dia, 1 as seq union all SELECT dateadd (day, +1, Dia), (seq +1) from Periodo where Dia < @Data_final ) SELECT Dia, seq into #Datas from Periodo; -- monta cabeçalho declare @Titulos varchar(500); set @Titulos= stuff ((SELECT distinct ', ' + T.titulo from (SELECT '[' + right ('0' + cast (seq as varchar(2)), 2) + '] as [' + convert (char(10), Dia, 103) + ']' as titulo from #Datas) as T order by ', ' + T.titulo asc for xml path('')), 1, 2, ''); PRINT @Titulos; --debug -- monta lista de valores referentes às datas declare @Valores varchar(500); set @Valores= stuff ((SELECT distinct ', ' + T.valor from (SELECT '[' + right ('0' + cast (seq as varchar(2)), 2) + ']' as valor from #Datas) as T order by ', ' + T.valor asc for xml path('')), 1, 2, ''); PRINT @Valores;  --debug -- monta texto do comando SQL em variável declare @ComandoSQL nvarchar(2000); set @ComandoSQL= N'with Marcacoes as (' + N'SELECT O.NOME_OPERADOR, right (''0'' + cast (seq as varchar(2)), 2) as seq, ' + N'case when M.ID_OPERADOR is null then '''' else ''X'' end as Escalado ' + N'from tbOperador as O ' + N'cross join #Datas as D ' + N'left join tbMarcacao as M on M.ID_OPERADOR = O.ID_OPERADOR ' + N'and D.Dia between M.DT_INICIO and M.DT_TERMINO' + N') ' + N'SELECT NOME_OPERADOR, ' + @Titulos + N' from Marcacoes ' + N'pivot (max (Escalado) for seq in (' + @Valores + ')) as P;'; -- executa o comando SQL PRINT @ComandoSQL;  --debug EXECUTE sp_executesql @ComandoSQL;

    --
    IF Object_ID ('tempDB..#Datas', 'U') is not null
      DROP TABLE #Datas;

    Substitua tbOperador pelo nome da tabela que contém os operadores e tbMarcacao pelo nome da tabela que contém as marcações.

    O código SQL acima está preparado para períodos de até 99 dias.



    Lembre-se de marcar esta resposta se ela te ajudou a resolver o problema.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Sugerido como Resposta José Diz sexta-feira, 13 de setembro de 2019 14:11
    • Editado José Diz quarta-feira, 25 de setembro de 2019 02:43
    sexta-feira, 6 de setembro de 2019 16:32
  • Paulo, teve a oportunidade de testar o código #2?

    Se o assunto foi resolvido, e alguma resposta deste tópico foi útil, poderia marcá-la(s)?

    • Editado José Diz sexta-feira, 13 de setembro de 2019 14:14
    sexta-feira, 13 de setembro de 2019 14:13
  • Boa tarde José, fiquei afastado do serviço esses dias, mais estou testando o codigo e ja retorno com uma posição.Obrigado mais uma vez pela ajuda.
    sexta-feira, 13 de setembro de 2019 17:07
  • José, testei aqui porem não rolou. 
    sexta-feira, 13 de setembro de 2019 17:21
  • Paulo, na geração de @ComandoSQL estava Datas quando o correto é #Datas. Já corrigido no código #2.

    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    quarta-feira, 25 de setembro de 2019 02:45