Agrupar e somar

Question

Bom dia !

Estou precisando novamente da ajuda de vocês.

Tenho a seguinte estrutura de view:

Exemplo

Empresa | Filial | Linha_neg | desc_lnegoc|  Grupo | desc _grupo    | competencia | custo_geral

01           |  01   |  1              | TI                 |  2        | CONTRATO TI   | 201601         | 50

01           |  01   |  1              | TI                 |  3        | CONTRATO TI2 | 201601         | 20

01           |  01   |  2              | jurídico         |  4        | GERENCIA   JU  | 201601        | 30

01           |  02   |  1             | LIMPEZA       |  2        | CONTRATO LIMP| 201601       | 50

03           |  01   |  3             | ALMOX         |  1         |CONTRATO LIMP | 201601       | 50

03           |  01   |  1             | LIMPEZA       |  2        | CONTRATO LIMP | 201601       | 50

03           |  02   |  2              | TI                 |  3        | CONTRATO TI2   | 201601         | 20

Estou tentando montar a seguinte estrutura mas ainda nao consegui:

Empresa | Filial | Linha_neg | desc_lnegoc|  Grupo | desc _grupo    | competencia | custo_geral | soma_filial | Rateio

01           |  01   |  1              | TI                 |  2        | CONTRATO TI   | 201601         | 50               |  80             | 50 /80

01           |  01   |  1              | TI                 |  3        | CONTRATO TI2 | 201601         | 20               |   80            |  20 /80

01           |  01   |  2              | jurídico         |  4        | GERENCIA   JU  | 201601        | 30               |   80            | 30 /80

01           |  02   |  1             | LIMPEZA       |  2        | CONTRATO LIMP | 201601       | 50                |  50            | 50 /50

03           |  01   |  3             | ALMOX         |  1       | CONTRATO LIMP | 201601       | 50                  | 100           | 50 /100

03           |  01   |  1             | LIMPEZA       |  2        | CONTRATO LIMP | 201601       | 50                | 100           | 50 /100

03           |  02   |  2              | TI                 |  3        | CONTRATO TI2 | 201601         | 20                | 20             | 20 /20

Rateio =  custo_gera / soma_filial

Alguém poderia me ajudar?

Estou montando select dessa forma porem ainda nao consegui incluir esses dois campos, pois ele só repete o valor do custo geral.

SELECT DISTINCT G.empresa, G.filial_atribuida, P.IDFILTRO, FGC.nome,P.IDGCC, G.DescGrupoCentroCusto,  G.DescGrupoCentroCusto, P.D,
          SUM (P.D) as SOMA_CONTRATO
       
FROM plano_estrategico P
INNER JOIN FiltroGrupo FGC ON FGC.id = P.IDFILTRO
INNER JOIN GrupoCentroCusto G ON G.idGrupoCentroCusto = P.IDGCC 
WHERE P.COMPETENCIA = '201601'
AND G.empresa = '01' AND G.filial_atribuida = '01'
GROUP BY  G.empresa, G.filial_atribuida, P.IDFILTRO,  FGC.nome, P.IDGCC, G.DescGrupoCentroCusto,  FGC.nome, G.DescGrupoCentroCusto, P.D

Answer 1

Boa tarde,

Experimente fazer uns testes dessa forma:

with CTE_Sum as
(
    SELECT DISTINCT 
        G.empresa, 
        G.filial_atribuida, 
        P.IDFILTRO, 
        FGC.nome,
        P.IDGCC, 
        G.DescGrupoCentroCusto,  
        P.D,
        SUM(P.D) OVER(PARTITION BY G.empresa, G.filial_atribuida) as SOMA_CONTRATO
    FROM plano_estrategico P
    INNER JOIN FiltroGrupo FGC ON FGC.id = P.IDFILTRO
    INNER JOIN GrupoCentroCusto G ON G.idGrupoCentroCusto = P.IDGCC 
    WHERE 
        P.COMPETENCIA = '201601' AND 
        G.empresa = '01' AND 
        G.filial_atribuida = '01'
)

SELECT 
    *,
    D / SOMA_CONTRATO AS Rateio
FROM CTE_Sum

Espero que ajude

---

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

Answer 2

O select funcionou perfeitamente. No caso agora só deixo essa estrutura dentro da view?
create view rateio  as (

coloco esse select?

)

Answer 3

Outro coisa ... Nesse select ele está listando e trazendo a soma total por filial

se eu quisesse que ele mostrasse além do valor total por filial trouxesse  o valor total por linha de negocio em outra coluna seria basicamente incluir essa linha de comando?

SUM(P.D) OVER(PARTITION BY G.empresa, G.filial_atribuida,P.IDGCC) as SOMA_CONTRATO_NEG,

ficando assim?

with CTE_Sum as
(
    SELECT DISTINCT 
        G.empresa, 
        G.filial_atribuida, 
        P.IDFILTRO, 
        FGC.nome,
        P.IDGCC, 
        G.DescGrupoCentroCusto,  
        P.D,
/*linha incluida*/
		SUM(P.D) OVER(PARTITION BY G.empresa, G.filial_atribuida,P.IDGCC) as SOMA_CONTRATO_NEG,



        SUM(P.D) OVER(PARTITION BY G.empresa, G.filial_atribuida) as SOMA_CONTRATO_FILIAL
    FROM plano_estrategico P
    INNER JOIN FiltroGrupo FGC ON FGC.id = P.IDFILTRO
    INNER JOIN GrupoCentroCusto G ON G.idGrupoCentroCusto = P.IDGCC 
    WHERE 
        P.COMPETENCIA = '201601' AND 
       G.empresa = '01' AND 
        G.filial_atribuida = '01'
)

SELECT 
    *,
    (D / SOMA_CONTRATO)* AS Rateio
FROM CTE_Sum

Fiz isso e nao funcionou.

Como poderia fazer para incluir nesse mesmo comando essa outra coluna ?

Answer 4

Me explica por que o valor  da coluna  soma_filial da 80 nesse caso

o certo não seria  (50 + 20 + 30)

Empresa | Filial | Linha_neg | desc_lnegoc|  Grupo | desc _grupo    | competencia | custo_geral

01           |  01   |  1              | TI                 |  2        | CONTRATO TI   | 201601         | 50

01           |  01   |  1              | TI                 |  3        | CONTRATO TI2 | 201601         | 20

01           |  01   |  2              | jurídico         |  4        | GERENCIA   JU  | 201601        | 30

01           |  02   |  1             | LIMPEZA       |  2        | CONTRATO LIMP| 201601       | 50

03           |  01   |  3             | ALMOX         |  1         |CONTRATO LIMP | 201601       | 50

03           |  01   |  1             | LIMPEZA       |  2        | CONTRATO LIMP | 201601       | 50

03           |  02   |  2              | TI                 |  3        | CONTRATO TI2   | 201601         | 20

Estou tentando montar a seguinte estrutura mas ainda nao consegui:

Empresa | Filial | Linha_neg | desc_lnegoc|  Grupo | desc _grupo    | competencia | custo_geral | soma_filial | Rateio

01           |  01   |  1              | TI                 |  2        | CONTRATO TI   | 201601         | 50               |  80             | 50 /80

01           |  01   |  1              | TI                 |  3        | CONTRATO TI2 | 201601         | 20               |   80            |  20 /80

01           |  01   |  2              | jurídico         |  4        | GERENCIA   JU  | 201601        | 30               |   80            | 30 /80

fora isso seque um exemplo com os dados que vc postou  não coloquei os filtros na parte do Where por que a coluna  (filial_atribuida)

não tem no seu exemplo

DECLARE @Table TABLE
    (
      Empresa VARCHAR(2) ,
      Filial VARCHAR(2) ,
      Linha_neg INT ,
      desc_lnegoc VARCHAR(MAX) ,
      Grupo INT ,
      desc_grupo VARCHAR(MAX) ,
      competencia VARCHAR(MAX) ,
      custo_geral INT
    );

INSERT  INTO @Table
VALUES  ( '01', '01', 1, 'TI', 2, 'CONTRATO TI', '201601', 50 ),
        ( '01', '01', 1, 'TI', 3, 'CONTRATO TI2', '201601', 20 ),
        ( '01', '01', 2, 'jurídico', 4, 'GERENCIA   JU', '201601', 30 ),
        ( '01', '02', 1, 'LIMPEZA', 2, 'CONTRATO LIMP', '201601', 50 ),
        ( '03', '01', 3, 'ALMOX', 1, 'CONTRATO LIMP', '201601', 50 ),
        ( '03', '01', 1, 'LIMPEZA', 2, 'CONTRATO LIMP', '201601', 50 ),
        ( '03', '02', 2, 'TI', 3, 'CONTRATO TI2', '201601', 20 );


WITH Dados AS(
SELECT  T.Empresa ,
        T.Filial ,
        T.Linha_neg ,
        T.desc_lnegoc ,
        T.Grupo ,
        T.desc_grupo ,
        T.competencia ,
        T.custo_geral ,
        [Soma Filial] = SUM(T.custo_geral) OVER ( PARTITION BY T.Empresa,
                                                  T.Filial )

FROM    @Table AS T
) 

SELECT R.Empresa ,
       R.Filial ,
       R.Linha_neg ,
       R.desc_lnegoc ,
       R.Grupo ,
       R.desc_grupo ,
       R.competencia ,
       R.custo_geral ,
       R.[Soma Filial],
	   [Rateio] =  CAST(ROUND(CAST(R.custo_geral AS DECIMAL(18,2)) / CAST(R.[Soma Filial] AS DECIMAL(18,2)),2) AS NUMERIC(18,2))  FROM Dados R

---

Wesley Neves

Answer 5

Pra criar uma view com essas informações é só copiar o select  normal da cte?

Answer 6

Carine,

Pode ser sim!!!!

---

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

Answer 7

Obrigada!!
Me ajudou muito mesmo