none
Acumular valores no sqlserver quebrando por colaborador RRS feed

  • Pergunta

  • Bom dia,

       Estou com dificuldades para gerar um comando que me traga todos os valores acumulados por colaborador, abaixo vou descrever o cenário para um melhor entendimento;

    Temos um ERP que executa o controle do ponto dos colaboradores, nesse controle o sistema armazena as informações de número do cadastro, data de lançamento, origem de lançamento, sinal de lançamento do valor (positivo ou negativo), quantidade de horas débito ou crédito do banco de horas e também a data limite para compensação. Estou tratando essas informações para listar no PowerBi, porem preciso criar um cenário de consulta que me liste todos os valores por colaborador acumulados por lançamento. Abaixo segue um esboço do comando que consegui gerar até o momento, porem se for executar esse comando em grande escala acabo gerando um erro no banco pela consulta ser muito grande. O que posso fazer para simplificar essa consulta? Devo criar uma procedure que armazena as informações com os resultados em alguma tabela? Devo ajustar essa seleção de informações para não apresentar mais erro no banco?

    with cte_dados as (
    SELECT          
    	NUMCAD,
    	DATLAN,
    	ORILAN,
    	sum((CASE
    	WHEN sinlan = '-' THEN (qtdhor *-1) 
                          ELSE (0)
    	END)) MinNeg,
    	sum((CASE
    	WHEN sinlan = '+' THEN (qtdhor *1) 
                          ELSE (0)
    	END)) MinPos,
    	DATCMP
    FROM Banco_Horas
    WHERE NUMEMP = 100
    AND NUMCAD in (2430,3100)
    AND	datlan between '21/03/2016' and '20/09/2016'
    GROUP BY NUMEMP,TIPCOL,NUMCAD,DATLAN,ORILAN,DATCMP
    ), cte_dados2 as (select ROW_NUMBER() over(partition by NUMCAD order by NUMCAD,DATLAN) numerador
    , NUMCAD, DATLAN, ORILAN, MINNEG, MINPOS, DATCMP from cte_dados)
    
    select NUMERADOR,NUMCAD,DATLAN,ORILAN,MINNEG,MINPOS,DATCMP,
    	isnull((select a.MINNEG + sum(b.MINNEG) from cte_dados2 b where b.numerador <= a.numerador-1 and b.NUMCAD = a.NUMCAD),a.MINNEG) as NegAcumulado,
    	isnull((select a.MINPOS + sum(c.MINPOS) from cte_dados2 c where c.numerador <= a.numerador-1 and c.NUMCAD = a.NUMCAD),a.MINPOS) as PosAcumulado	
    from cte_dados2 a
    order by 2,1;

    Agradeço desde já a colaboração de todos.

    Att,

    Rodrigo Hacke


    • Editado Rodrigo Hacke terça-feira, 11 de outubro de 2016 14:48
    terça-feira, 11 de outubro de 2016 14:41

Respostas

Todas as Respostas

  • Deleted
    terça-feira, 11 de outubro de 2016 15:16
  • José,

    Segue abaixo as informações que solicitou.

    Na coluna NUMCAD vai a informação do código do colaborador ex; 2430 e 3100. Já na coluna NUMEMP vai o código da empresa cadastrada ex: 100. Na coluna TIPCOL vai a informação do tipo do colaborador ex: 1 - Empregado, 2 - Terceiro e 3 - Parceiro.

    Segue abaixo o script do único índice criado;

    ALTER TABLE [dbo].[Banco_Horas] ADD  CONSTRAINT [cp_Banco_Horas] PRIMARY KEY NONCLUSTERED 
    (
    	[numemp] ASC,
    	[tipcol] ASC,
    	[numcad] ASC,
    	[codbhr] ASC,
    	[datlan] ASC,
    	[codsit] ASC,
    	[orilan] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    Possuímos a versão SQL Server 2012

    Agradeço pela dica na construção do SQL no CASE criado. Vou colocar ele em prática.

    terça-feira, 11 de outubro de 2016 16:25
  • Rodrigo,

    Então você tem uma chave primária composta!!!

    Realmente é necessário a existência da chave desta maneira?

    Qual é a relação entre os dados dos funcionários e os valores gerados por eles?


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 11 de outubro de 2016 17:27
  • Junior Galvão,

    Respondendo sua primeira pergunta o que posso dizer é que esse sistema foi desenvolvido dessa forma não posso responder o por que da existência dessa chave, minha única função é encontrar uma forma de gerar um BI com os valores acumulados.

    A relação dos dados dos funcionários são os vínculos de cada cadastro com sua rotina de trabalho. Digamos que meu horário seja das 08:00 até 12:00 e 13:00 até 17:48 dessa forma qualquer horário a mais ou a menos dessa faixa apresentada vai ocasionar banco de horas ou débito do banco de horas. É nessa hora que são armazenada as informações na tabela Banco_Horas.

    Espero que possa ter ajudado em algo.

    terça-feira, 11 de outubro de 2016 17:41
  • Rodrigo,

    Ok, certo, então mas de alguma maneira os funcionários apontam estas atividades de que maneira?


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 11 de outubro de 2016 17:47
  • Junior Galvão,

    Essas atividades de são apontadas no momento em que o colaborador chega na empresa, nesse instante ele realiza o registro do ponto com um cartão magnético, esse processo é sempre registrado na entrada(08:00)/ na saída para o almoço(12:00) / no retorno do almoço(13:00) / e na saída da empresa(17:48). Caso aconteça do colaborador chegar para trabalhar na empresa as 10:00 automaticamente será salvo uma informação na tabela Banco_horas com a quantidade de 120 negativo. E caso sua entrada fosse as 06:00 seria lançado 120 positivo. O campo qtdhor trabalha com os valores em minuto, por isso se o exemplo que informei estava em 02:00 o sistema salvou 120 minutos.

    terça-feira, 11 de outubro de 2016 17:57
  • Rodrigo,

    Certo, que bom, era o que eu pensava, então temos um tabela que guarda todos os dados do colaborador, outra atividade que possui a lista de atividades realizadas e uma outra que guarda os apontamentos de horas.

    Basicamente é isso?


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 11 de outubro de 2016 18:07
  • Pedro,

    Sim exatamente isso.

    terça-feira, 11 de outubro de 2016 18:32
  • Boa tarde,

    Rodrigo, você pode postar um exemplo com uma amostra de dados da sua tabela e o respectivo resultado esperado?


    Assinatura: http://www.imoveisemexposicao.com.br

    terça-feira, 11 de outubro de 2016 20:17
  • Deleted
    quarta-feira, 12 de outubro de 2016 17:58
  • Bom dia José Diz,

       Desculpe a demora para responder ao fórum, gostaria de agradecer a sua colaboração no script que enviou como resposta. Acabo de voltar de férias e agora estou trabalhando no script e está listando as informações corretamente conforme havia mencionado acima. Se não for abusar existe alguma forma de usar esse mesmo script para dentro de uma Procedure para alimentar uma tabela com os resultados? Pois estou tentando trabalhar esse comando no BI e não estou tendo muito sucesso, no meu ponto de vista seria melhor salvar o resultado da consulta sql em uma tabela para depois eu usar o BI para consultar as informações dessa tabela.

    Desde já fico grato com a colaboração até o momento.

    quarta-feira, 9 de novembro de 2016 13:25
  • Deleted
    quinta-feira, 10 de novembro de 2016 10:57