 |
ActiveDelphi
.: O site do programador Delphi! :.
|
| Exibir mensagem anterior :: Exibir próxima mensagem |
| Autor |
Mensagem |
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
 Enviada: Sex Set 08, 2017 9:38 pm Assunto: Usando CTE no SQL Server 2008R? |
 |
|
Neste select aqui
| Código: | SELECT
i.codigo_cabecalho as cod_cabecalho,
I.codigo_mercadoria as cod_mercadoria,
m.nome as nome_merc,
i.quantidade_vendida as qtd_vendida,
i.valor_unitario as valor_unit,
SUM(i.valor_unitario * i.quantidade_vendida) as soma_itens
FROM dbo.ITENS i
inner join
dbo.cabecalho c on
c.codigo = i.codigo_cabecalho
INNER join
dbo.MERCADORIA m on
m.CODIGO = i.codigo_mercadoria
group by
I.CODIGO,
i.codigo_caBECALHO,
I.codigo_mercadoria,
m.nome,
i.quantidade_vendida,
i.valor_unitario |
O resultado é este
E quando uso CET para trazer o resultado da venda por dia...
| Código: | with cte_venda as( SELECT
i.codigo_caBECALHO as cod_cabecalho,
I.codigo_mercadoria as cod_mercadoria,
m.nome as nome_merc,
i.quantidade_vendida as qtd_vendida,
i.valor_unitario as valor_unit,
SUM(i.valor_unitario * i.quantidade_vendida) as soma_itens
FROM dbo.ITENS i
inner join
dbo.cabecalho c on
c.codigo = i.codigo_cabecalho
INNER join
dbo.MERCADORIA m on
m.CODIGO = i.codigo_mercadoria
group by
I.CODIGO,
i.codigo_caBECALHO,
I.codigo_mercadoria,
m.nome,
i.quantidade_vendida,
i.valor_unitario
)
select
case when cod_cabecalho is null
then '9999999'
else cod_cabecalho
end as cod_cabecalho,
cod_mercadoria,
case when cod_mercadoria is null
then
case when cod_cabecalho is null
then 'Total Geral'
else 'Total da Venda'
end
else nome_merc
end as nome_merc,
sum(qtd_vendida) as qtd_vendida,
valor_unit,
SUM(soma_itens) as soma_itens
from CTE_venda
group by
grouping sets
((cod_cabecalho, cod_mercadoria, nome_merc, qtd_vendida,
valor_unit, soma_itens), (cod_cabecalho), ())
ORDER BY cod_cabecalho |
o resultado é este
ele soma os itens que são idênticos, mais preciso que fique listado como a primeira imagem (Exemplo Tenho a lista de itens de venda de 2 x o delphi) mais nesta soma ele agrupa eles.
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
 |
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Sáb Set 09, 2017 7:03 pm Assunto: |
|
|
Boa tarde,
Acho que para evitar o agrupamento dos itens vai ser necessário adicionar um campo no Select e no Group By que possui um valor distinto. Acho a princípio que poderia ser o Id da tabela Itens.
obs: não verifiquei detalhadamente mas acho que a CTE não é necessária; me parece que você poderia fazer direto o Group By Grouping Sets
Espero que ajude
Editado pela última vez por imex em Dom Out 01, 2023 3:40 pm, num total de 1 vez |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
| Enviada: Seg Set 11, 2017 10:09 am Assunto: |
|
|
| imex escreveu: | Boa tarde,
Acho que para evitar o agrupamento dos itens vai ser necessário adicionar um campo no Select e no Group By que possui um valor distinto. Acho a princípio que poderia ser o Id da tabela Itens.
obs: não verifiquei detalhadamente mas acho que a CTE não é necessária; me parece que você poderia fazer direto o Group By Grouping Sets
Espero que ajude
|
Imex tenho na tabela cabecalho um campo chamado DESCONTO, tem como incluir na soma de cada TOTAL DA VENDA diminuindo do campo TOTAL uma soma?
Exemplo
Total da venda 1.000,00
Desconto 300,00
Total 700,00
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Seg Set 11, 2017 10:52 am Assunto: |
|
|
Acho que seria necessário uma CTE onde a query teria o Grouping Sets e o Desconto fora do Sum, e depois na query final seria utilizado o Case para verificar se é a linha de total e caso seja seria feita a subtração do desconto.
Espero que ajude |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Ter Set 12, 2017 10:39 am Assunto: |
|
|
| Existe algum campo na tabela Itens que permita identificar o primeiro item de cada Cabeçalho? |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
| Enviada: Ter Set 12, 2017 11:16 am Assunto: |
|
|
| imex escreveu: | | Existe algum campo na tabela Itens que permita identificar o primeiro item de cada Cabeçalho? |
Imex as tabelas envolvidas são estas (Estruturas)
| Código: | CREATE TABLE [dbo].[CABECALHO](
[CODIGO] [int] IDENTITY(1,1) NOT NULL,
[DATA] [date] NULL,
[CODIGO_CLIENTE] [int] NULL,
[DESCONTO] [decimal](10, 2) NULL,
[TOTAL] [decimal](10, 2) NULL,
CONSTRAINT [PK__CABECALH__CC87E1271273C1CD] PRIMARY KEY CLUSTERED
(
[CODIGO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ITENS](
[CODIGO] [int] IDENTITY(1,1) NOT NULL,
[CODIGO_CABECALHO] [int] NULL,
[CODIGO_MERCADORIA] [int] NULL,
[QUANTIDADE_VENDIDA] [decimal](18, 0) NULL,
[VALOR_UNITARIO] [decimal](18, 2) NULL,
CONSTRAINT [PK__ITENS__CC87E12724927208] PRIMARY KEY CLUSTERED
(
[CODIGO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MERCADORIA](
[CODIGO] [int] IDENTITY(1,1) NOT NULL,
[NOME] [varchar](60) NULL,
[PRECO] [money] NULL,
[ESTOQUE] [int] NULL,
PRIMARY KEY CLUSTERED
(
[CODIGO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CLIENTE](
[CODIGO] [int] IDENTITY(1,1) NOT NULL,
[NOME] [varchar](60) NULL,
PRIMARY KEY CLUSTERED
(
[CODIGO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
|
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Ter Set 12, 2017 11:40 am Assunto: |
|
|
Não sei se vai funcionar mas segue uma sugestão para teste:
| Código: | with
cte_venda as
(
SELECT
i.codigo_caBECALHO as cod_cabecalho,
I.codigo_mercadoria as cod_mercadoria,
m.nome as nome_merc,
i.quantidade_vendida as qtd_vendida,
i.valor_unitario as valor_unit,
i.valor_unitario * i.quantidade_vendida as soma_itens,
i.Codigo,
min(i.Codigo) over(partition by codigo_caBECALHO) as CodigoMin
FROM dbo.ITENS i
INNER join dbo.MERCADORIA m
on m.CODIGO = i.codigo_mercadoria
),
cte_desconto as
(
select
v.*,
case when v.Codigo = v.CodigoMin
then (select c.Desconto from dbo.cabecalho as c where c.codigo = v.codigo_cabecalho)
else 0
end as Desconto
from cte_venda as v
)
select
case when cod_cabecalho is null
then '9999999'
else cod_cabecalho
end as cod_cabecalho,
cod_mercadoria,
case when cod_mercadoria is null
then
case when cod_cabecalho is null
then 'Total Geral'
else 'Total da Venda'
end
else nome_merc
end as nome_merc,
qtd_vendida,
valor_unit,
sum(soma_itens) -
case when cod_mercadoria is null
then sum(Desconto)
else 0
end as soma_itens
from CTE_venda
group by grouping sets
( (CODIGO, codigo_caBECALHO, codigo_mercadoria, nome, qtd_vendida, valor_unit),
(cod_cabecalho),
()
) |
Espero que ajude |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
| Enviada: Ter Set 12, 2017 11:47 am Assunto: |
|
|
Estou testando aqui
Surgiu um erro aqui
| Código: | select
v.*,
case when v.Codigo = v.CodigoMin
then (select c.Desconto from
[NoxAutomacaoTeste].dbo.cabecalho as c
where c.codigo = v.codigo_cabecalho)
else 0
end as Desconto
from cte_venda as v |
| Citação: | Mensagem 207, Nível 16, Estado 1, Linha 25
Nome de coluna 'codigo_cabecalho' inválido. |
Aqui também
| Código: | | then sum(Desconto) |
mostra NOME DA COLUNA INVÁLIDO
E por ultimo
aqui
| Código: | group by grouping sets
( (CODIGO, codigo_caBECALHO, codigo_mercadoria, nome, qtd_vendida, valor_unit),
(cod_cabecalho),
()
) |
Estes 3 campos codigo_caBECALHO, codigo_mercadoria, nome também mostra como NOME DA COLUNA INVALIDO
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Ter Set 12, 2017 12:00 pm Assunto: |
|
|
Experimente dessa forma:
| Código: | with
cte_venda as
(
SELECT
i.codigo_caBECALHO as cod_cabecalho,
I.codigo_mercadoria as cod_mercadoria,
m.nome as nome_merc,
i.quantidade_vendida as qtd_vendida,
i.valor_unitario as valor_unit,
i.valor_unitario * i.quantidade_vendida as soma_itens,
i.Codigo,
min(i.Codigo) over(partition by codigo_caBECALHO) as CodigoMin
FROM dbo.ITENS i
INNER join dbo.MERCADORIA m
on m.CODIGO = i.codigo_mercadoria
),
cte_desconto as
(
select
v.*,
case when v.Codigo = v.CodigoMin
then (select c.Desconto from dbo.cabecalho as c where c.codigo = v.cod_cabecalho)
else 0
end as Desconto
from cte_venda as v
)
select
case when cod_cabecalho is null
then '9999999'
else cod_cabecalho
end as cod_cabecalho,
cod_mercadoria,
case when cod_mercadoria is null
then
case when cod_cabecalho is null
then 'Total Geral'
else 'Total da Venda'
end
else nome_merc
end as nome_merc,
qtd_vendida,
valor_unit,
sum(soma_itens) -
case when cod_mercadoria is null
then sum(Desconto)
else 0
end as soma_itens
from cte_desconto
group by grouping sets
( (CODIGO, cod_caBECALHO, cod_mercadoria, nome_merc, qtd_vendida, valor_unit),
(cod_cabecalho),
()
) |
Espero que ajude |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Qua Set 13, 2017 10:31 am Assunto: |
|
|
Fiz alguns testes e obtive o resultado esperado.
Alterei um pouco a query apenas para ter os valores separados e facilitar a conferência.
Segue abaixo o script para teste através do SQL Server Management Studio:
| Código: | declare @CABECALHO TABLE (
[CODIGO] [int] IDENTITY(1,1) NOT NULL,
[DATA] [date] NULL,
[CODIGO_CLIENTE] [int] NULL,
[DESCONTO] [decimal](10, 2) NULL,
[TOTAL] [decimal](10, 2) NULL);
declare @ITENS TABLE (
[CODIGO] [int] IDENTITY(1,1) NOT NULL,
[CODIGO_CABECALHO] [int] NULL,
[CODIGO_MERCADORIA] [int] NULL,
[QUANTIDADE_VENDIDA] [decimal](18, 0) NULL,
[VALOR_UNITARIO] [decimal](18, 2) NULL);
declare @MERCADORIA TABLE (
[CODIGO] [int] IDENTITY(1,1) NOT NULL,
[NOME] [varchar](60) NULL,
[PRECO] [money] NULL,
[ESTOQUE] [int] NULL);
insert into @MERCADORIA (Nome, Preco, Estoque) values
('MERCADORIA 1', 100, 10),
('MERCADORIA 2', 200, 20),
('MERCADORIA 3', 300, 30);
INSERT INTO @CABECALHO (DATA, CODIGO_CLIENTE, DESCONTO, TOTAL) VALUES
('20170912', 1, 10, 60),
('20170912', 2, 0, 130),
('20170913', 3, 5, 500);
INSERT INTO @ITENS (CODIGO_CABECALHO, CODIGO_MERCADORIA, QUANTIDADE_VENDIDA, VALOR_UNITARIO) VALUES
(1, 1, 1, 10),
(1, 2, 1, 20),
(1, 3, 1, 30),
(2, 2, 2, 20),
(2, 3, 3, 30),
(3, 1, 10, 10),
(3, 2, 20, 20);
with
cte_venda as
(
SELECT
i.codigo_caBECALHO as cod_cabecalho,
I.codigo_mercadoria as cod_mercadoria,
m.nome as nome_merc,
i.quantidade_vendida as qtd_vendida,
i.valor_unitario as valor_unit,
i.valor_unitario * i.quantidade_vendida as soma_itens,
i.Codigo,
min(i.Codigo) over(partition by codigo_caBECALHO) as CodigoMin
FROM @ITENS i
INNER join @MERCADORIA m
on m.CODIGO = i.codigo_mercadoria
),
cte_desconto as
(
select
v.*,
case when v.Codigo = v.CodigoMin
then (select c.Desconto from @cabecalho as c where c.codigo = v.cod_cabecalho)
else 0
end as Desconto
from cte_venda as v
)
select
case when cod_cabecalho is null
then '9999999'
else cod_cabecalho
end as cod_cabecalho,
cod_mercadoria,
case when cod_mercadoria is null
then
case when cod_cabecalho is null
then 'Total Geral'
else 'Total da Venda'
end
else nome_merc
end as nome_merc,
qtd_vendida,
valor_unit,
sum(soma_itens) as soma_itens,
case when cod_mercadoria is null
then sum(Desconto)
end as Desconto,
case when cod_mercadoria is null
then sum(soma_itens) - sum(Desconto)
end as Liquido
from cte_desconto
group by grouping sets
( (CODIGO, cod_caBECALHO, cod_mercadoria, nome_merc, qtd_vendida, valor_unit),
(cod_cabecalho),
()
) |
Espero que ajude |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Qua Set 13, 2017 11:06 am Assunto: |
|
|
Fiz um teste removendo o Insert de um item do script de teste para que o cabeçalho ficasse com um único item e obtive o total normalmente.
| Código: | INSERT INTO @ITENS (CODIGO_CABECALHO, CODIGO_MERCADORIA, QUANTIDADE_VENDIDA, VALOR_UNITARIO) VALUES
(1, 1, 1, 10),
(1, 2, 1, 20),
(1, 3, 1, 30),
(2, 2, 2, 20),
(2, 3, 3, 30),
--(3, 1, 10, 10),
(3, 2, 20, 20); |
|
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
|
| Voltar ao Topo |
|
|
|
|
Enviar Mensagens Novas: Proibido.
Responder Tópicos Proibido
Editar Mensagens: Proibido.
Excluir Mensagens: Proibido.
Votar em Enquetes: Proibido.
|
|
FAQ
Pesquisar
Membros
Grupos
Registrar
Perfil
Entrar e ver Mensagens Particulares
Entrar
