Criar uma view e usar sum

Question

Boa tarde,

Preciso muito da ajuda de vocês para criar uma view entre duas tabelas.

CREATE TABLE ORCAMENTO (
ID_ORCAMENTO		VARCHAR(1) NOT NULL,	
CODIGO_ORC			VARCHAR(3),
ID_ORCAMENTO_ORIG	VARCHAR(1)  --ID DO ORCAMENTO PRINCIPAL
CONSTRAINT PK_ID_ORC PRIMARY KEY (ID_ORCAMENTO)
)

INSERT INTO ORCAMENTO (ID_ORCAMENTO,CODIGO_ORC,ID_ORCAMENTO_ORIG) VALUES ('1','001',NULL)
INSERT INTO ORCAMENTO (ID_ORCAMENTO,CODIGO_ORC,ID_ORCAMENTO_ORIG) VALUES ('2','002','1')
INSERT INTO ORCAMENTO (ID_ORCAMENTO,CODIGO_ORC,ID_ORCAMENTO_ORIG) VALUES ('3','003',NULL)
INSERT INTO ORCAMENTO (ID_ORCAMENTO,CODIGO_ORC,ID_ORCAMENTO_ORIG) VALUES ('4','004','1')

CREATE TABLE ITENS_ORCAMENTO (
ID_ITENS			VARCHAR(1) NOT NULL,	
ID_ORCAMENTO		VARCHAR(1) NOT NULL,
QUANT				INT, -- SOMAR ITENS IGUAIS DOS ORCAMENTOS E COMPLEMENTO DOS ORCAMENTOS
PRODUTO				VARCHAR(30)
CONSTRAINT PK_ID_ITENS PRIMARY KEY (ID_ITENS)	
CONSTRAINT FK_ID_ORCAMENTO FOREIGN KEY (ID_ORCAMENTO) REFERENCES ORCAMENTO (ID_ORCAMENTO), 
)			


INSERT INTO ITENS_ORCAMENTO (ID_ITENS, ID_ORCAMENTO, QUANT, PRODUTO) VALUES ('1','1',2,'LIVRO')
INSERT INTO ITENS_ORCAMENTO (ID_ITENS, ID_ORCAMENTO, QUANT, PRODUTO) VALUES ('2','1',1,'CANETA')
INSERT INTO ITENS_ORCAMENTO (ID_ITENS, ID_ORCAMENTO, QUANT, PRODUTO) VALUES ('3','1',3,'BORRACHA')
									   
INSERT INTO ITENS_ORCAMENTO (ID_ITENS, ID_ORCAMENTO, QUANT, PRODUTO) VALUES ('4','2',1,'LIVRO')
INSERT INTO ITENS_ORCAMENTO (ID_ITENS, ID_ORCAMENTO, QUANT, PRODUTO) VALUES ('5','2',1,'CANETA')
									   
INSERT INTO ITENS_ORCAMENTO (ID_ITENS, ID_ORCAMENTO, QUANT, PRODUTO) VALUES ('6','3',3,'LIVRO')

INSERT INTO ITENS_ORCAMENTO (ID_ITENS, ID_ORCAMENTO, QUANT, PRODUTO) VALUES ('7','4',1,'CANETA')


CREATE VIEW ITENS_AUTORIZACAO 
AS
SELECT 
SUM(ITENS_ORCAMENTO.QUANT)			AS QUANIDADE,
ITENS_ORCAMENTO.PRODUTO				AS PRODUTO,
ORCAMENTO.CODIGO_ORC				AS ORCAMENTO_PRINCIPAL   -- AQUI QUE NAO ESTOU CONSGUINTO TRAZER O CODIGO DO ORCAMENTO PRINCIPAL
 
FROM ITENS_ORCAMENTO
INNER JOIN ORCAMENTO ON ITENS_ORCAMENTO.ID_ORCAMENTO = ORCAMENTO.ID_ORCAMENTO
GROUP BY ITENS_ORCAMENTO.PRODUTO, ORCAMENTO.CODIGO_ORC



Esta saindo assim. Porem o orcamento 002 e 004 são complementos do orcamento 001. Devia somar os orcamentos 001, 002 e 004.

OBS. Não posso alterar a estrutura das tabelas. tenho que alterar somente na view.

Answer 1

Desculpa mais não consegui entender a saida final ?

qual seria o resultado correto encima desses dados que vc postou ?

---

Wesley Neves

Answer 2

Deleted

Answer 3

Boa tarde,

Experimente dessa forma:

SELECT 
    SUM(ITENS_ORCAMENTO.QUANT) AS QUANIDADE,
    ITENS_ORCAMENTO.PRODUTO AS PRODUTO,
    ISNULL(ORC_PRINC.CODIGO_ORC, ORCAMENTO.CODIGO_ORC) AS ORCAMENTO_PRINCIPAL
FROM ITENS_ORCAMENTO
INNER JOIN ORCAMENTO ON ITENS_ORCAMENTO.ID_ORCAMENTO = ORCAMENTO.ID_ORCAMENTO
LEFT JOIN ORCAMENTO AS ORC_PRINC ON ORC_PRINC.ID_ORCAMENTO = ORCAMENTO.ID_ORCAMENTO_ORIG
GROUP BY 
    ITENS_ORCAMENTO.PRODUTO, 
    ISNULL(ORC_PRINC.CODIGO_ORC, ORCAMENTO.CODIGO_ORC)

Espero que ajude

---

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

Answer 4

Deleted

Answer 5

Boa tarde,

Experimente dessa forma:

SELECT 
    SUM(ITENS_ORCAMENTO.QUANT) AS QUANIDADE,
    ITENS_ORCAMENTO.PRODUTO AS PRODUTO,
    ISNULL(ORC_PRINC.CODIGO_ORC, ORCAMENTO.CODIGO_ORC) AS ORCAMENTO_PRINCIPAL
FROM ITENS_ORCAMENTO
INNER JOIN ORCAMENTO ON ITENS_ORCAMENTO.ID_ORCAMENTO = ORCAMENTO.ID_ORCAMENTO
LEFT JOIN ORCAMENTO AS ORC_PRINC ON ORC_PRINC.ID_ORCAMENTO = ORCAMENTO.ID_ORCAMENTO_ORIG
GROUP BY 
    ITENS_ORCAMENTO.PRODUTO, 
    ISNULL(ORC_PRINC.CODIGO_ORC, ORCAMENTO.CODIGO_ORC)

Espero que ajude

---

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

Vou analisar melhor amanhã pois no exemplo deu certinho mais quando passei para produção não deu.

Answer 6

Adalmir, considerando o exemplo que você postou, podem existir orçamentos originados a partir dos orçamentos 2 e 4?

Se podem e eles devem ser somados no orçamento 1, experimente fazer uns testes dessa forma:

with CTE_Rec as
(
    select
        ID_ORCAMENTO,
        CODIGO_ORC,
        ID_ORCAMENTO_ORIG
    from @ORCAMENTO
    
    union all
    
    select
        c.ID_ORCAMENTO,
        o.CODIGO_ORC,
        o.ID_ORCAMENTO_ORIG
    from CTE_Rec as c
    inner join @ORCAMENTO as o
        on o.ID_ORCAMENTO = c.ID_ORCAMENTO_ORIG
    where 
        c.ID_ORCAMENTO_ORIG IS NOT NULL
)

SELECT 
    SUM(i.QUANT) AS QUANIDADE,
    i.PRODUTO,
    c.CODIGO_ORC AS ORCAMENTO_PRINCIPAL
FROM ITENS_ORCAMENTO
INNER JOIN CTE_Rec as c 
    ON 
        i.ID_ORCAMENTO = c.ID_ORCAMENTO and
        c.ID_ORCAMENTO_ORIG IS NULL
GROUP BY 
    i.PRODUTO, 
    c.CODIGO_ORC

Espero que ajude

---

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

Answer 7

Adalmir, considerando o exemplo que você postou, podem existir orçamentos originados a partir dos orçamentos 2 e 4?

Se podem e eles devem ser somados no orçamento 1, experimente fazer uns testes dessa forma:

with CTE_Rec as
(
    select
        ID_ORCAMENTO,
        CODIGO_ORC,
        ID_ORCAMENTO_ORIG
    from @ORCAMENTO
    
    union all
    
    select
        c.ID_ORCAMENTO,
        o.CODIGO_ORC,
        o.ID_ORCAMENTO_ORIG
    from CTE_Rec as c
    inner join @ORCAMENTO as o
        on o.ID_ORCAMENTO = c.ID_ORCAMENTO_ORIG
    where 
        c.ID_ORCAMENTO_ORIG IS NOT NULL
)

SELECT 
    SUM(i.QUANT) AS QUANIDADE,
    i.PRODUTO,
    c.CODIGO_ORC AS ORCAMENTO_PRINCIPAL
FROM ITENS_ORCAMENTO
INNER JOIN CTE_Rec as c 
    ON 
        i.ID_ORCAMENTO = c.ID_ORCAMENTO and
        c.ID_ORCAMENTO_ORIG IS NULL
GROUP BY 
    i.PRODUTO, 
    c.CODIGO_ORC

Espero que ajude

---

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

Consegui com o script anterior que você me passou. Eu estava colocando o campo itens no group by também ai não estava somando corretamente.

Ja abusando um pouco da sua ajuda poderia me explicar o que essa parte do script esta fazendo?

 ISNULL(ORC_PRINC.CODIGO_ORC, ORCAMENTO.CODIGO_ORC) AS ORCAMENTO_PRINCIPAL
LEFT JOIN ORCAMENTO AS ORC_PRINC ON ORC_PRINC.ID_ORCAMENTO = ORCAMENTO.ID_ORCAMENTO_ORIG

Answer 8

O Left Join foi utilizado para obter o CODIGO_ORC do orçamento original quando a coluna ID_ORCAMENTO_ORIG estiver preenchida, e o IsNull foi utilizado porque ORC_PRINC.CODIGO_ORC será nulo quando ID_ORCAMENTO_ORIG não estiver preenchida, caso em que é retornado o valor de ORCAMENTO.CODIGO_ORC.

Espero que ajude

---

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

Answer 9

Adalmir,

O uso da cláusula Group By vai justamente influenciar no retorno e resultado, neste caso você tem que analisar quais colunas e agrupamentos de dados você deseja fazer.

---

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]