Stack Overflow em Português Asked by Bruno Araujo Santana on December 30, 2020
Estou montando uma estrutura dentro de uma view onde estou trabalhando com níveis mercadológicos (segmento do varejo). Seguindo essa ideia tenho um produto que pode conter mais de um registro vigente; pode conter, por exemplo, a seção A com 10% de desconto e a marca B com 15% e em uma linha a nível de produto um produto X com desconto de 8%.
Preciso que o Retorno seja caso eu selecione o Produto X o valor de desconto seja 8% (mesmo que dentro deste produto contenha a seção A, e Marca B).
Quando eu selecionar um produto da marca B deve retornar 15% (desde que não esteja cadastrado no mesmo registro na "aba" de produtos e independente se estiver cadastrado na aba de Seções).
Quando eu selecionar algum produto da Seção A, este produto não pode estar "dentro" da marca B e nem dentro das demais classificações.
Existem outras regras dentro da View que são mais tranquilas são não consegui resolver este ponto acima.
Lembrando que preciso sempre considerar o maior registro cadastrado Vigente entre o produto escolhido, e o menor nível mercadológico parametrizado.
A priorização é da esquerda para direita:
Produto / Marca / Classes / Subgrupo / Grupo / Seções
Segue view para quem topar dar um toque:
SELECT A.TABELA_DESCONTO ,
Z.PRODUTO ,
A.TIPO_CALCULO_PRECO_PROMOCAO ,
CASE WHEN A.TIPO_CALCULO_PRECO_PROMOCAO = 1
THEN A.DESCONTO_PADRAO
ELSE 0
END AS DESCONTO_PADRAO ,
A.DATA_HORA_INICIAL,
A.DATA_HORA_FINAL,
CASE WHEN A.TIPO_CALCULO_PRECO_PROMOCAO = 2
THEN A.LUCRO_BRUTO_TABELADO
ELSE 0
END AS LUCRO_BRUTO_TABELADO,
A.APENAS_CLIENTES,
A.APENAS_CONVENIOS
FROM TABELAS_DESCONTOS_SECOES A WITH(NOLOCK)
JOIN MAIOR_TABELA_DESCONTO J WITH(NOLOCK) ON J.TABELA_DESCONTO = A.TABELA_DESCONTO
JOIN PRODUTOS Z WITH(NOLOCK) ON Z.SECAO_PRODUTO = A.SECAO_PRODUTO
LEFT
JOIN TABELAS_DESCONTOS_GRUPOS C (NOLOCK) ON C.GRUPO_PRODUTO = Z.GRUPO_PRODUTO
LEFT
JOIN TABELAS_DESCONTOS_SUBGRUPOS D (NOLOCK) ON D.SUBGRUPO_PRODUTO = Z.SUBGRUPO_PRODUTO
LEFT
JOIN TABELAS_DESCONTOS_CLASSES E (NOLOCK) ON E.CLASSE_PRODUTO = Z.CLASSE_PRODUTO
LEFT
JOIN TABELAS_DESCONTOS_MARCAS F (NOLOCK) ON F.MARCA = Z.MARCA
LEFT
JOIN TABELAS_DESCONTOS_MARKUP G (NOLOCK) ON G.PRODUTO = Z.PRODUTO
WHERE GETDATE() BETWEEN A.DATA_HORA_INICIAL AND A.DATA_HORA_FINAL
AND ( ( DATEPART(DW, GETDATE() ) = 1 AND A.DOMINGO = 'S') OR
( DATEPART(DW, GETDATE() ) = 2 AND A.SEGUNDA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 3 AND A.TERCA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 4 AND A.QUARTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 5 AND A.QUINTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 6 AND A.SEXTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 7 AND A.SABADO = 'S') )
AND C.GRUPO_PRODUTO IS NULL
AND D.SUBGRUPO_PRODUTO IS NULL
AND E.CLASSE_PRODUTO IS NULL
AND F.MARCA IS NULL
AND G.PRODUTO IS NULL
UNION ALL
SELECT A.TABELA_DESCONTO ,
Z.PRODUTO ,
A.TIPO_CALCULO_PRECO_PROMOCAO ,
CASE WHEN A.TIPO_CALCULO_PRECO_PROMOCAO = 1
THEN A.DESCONTO_PADRAO
ELSE 0
END AS DESCONTO_PADRAO ,
A.DATA_HORA_INICIAL,
A.DATA_HORA_FINAL,
CASE WHEN A.TIPO_CALCULO_PRECO_PROMOCAO = 2
THEN A.LUCRO_BRUTO_TABELADO
ELSE 0
END AS LUCRO_BRUTO_TABELADO,
A.APENAS_CLIENTES,
A.APENAS_CONVENIOS
FROM TABELAS_DESCONTOS_GRUPOS A WITH(NOLOCK)
JOIN MAIOR_TABELA_DESCONTO J WITH(NOLOCK) ON J.TABELA_DESCONTO = A.TABELA_DESCONTO
JOIN PRODUTOS Z WITH(NOLOCK) ON Z.GRUPO_PRODUTO = A.GRUPO_PRODUTO
LEFT
JOIN TABELAS_DESCONTOS_SUBGRUPOS D (NOLOCK) ON D.SUBGRUPO_PRODUTO = Z.SUBGRUPO_PRODUTO
LEFT
JOIN TABELAS_DESCONTOS_CLASSES E (NOLOCK) ON E.CLASSE_PRODUTO = Z.CLASSE_PRODUTO
LEFT
JOIN TABELAS_DESCONTOS_MARCAS F (NOLOCK) ON F.MARCA = Z.MARCA
LEFT
JOIN TABELAS_DESCONTOS_MARKUP G (NOLOCK) ON G.PRODUTO = Z.PRODUTO
WHERE GETDATE() BETWEEN A.DATA_HORA_INICIAL AND A.DATA_HORA_FINAL
AND ( ( DATEPART(DW, GETDATE() ) = 1 AND A.DOMINGO = 'S') OR
( DATEPART(DW, GETDATE() ) = 2 AND A.SEGUNDA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 3 AND A.TERCA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 4 AND A.QUARTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 5 AND A.QUINTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 6 AND A.SEXTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 7 AND A.SABADO = 'S') )
AND D.SUBGRUPO_PRODUTO IS NULL
AND E.CLASSE_PRODUTO IS NULL
AND F.MARCA IS NULL
AND G.PRODUTO IS NULL
UNION ALL
SELECT A.TABELA_DESCONTO ,
Z.PRODUTO ,
A.TIPO_CALCULO_PRECO_PROMOCAO ,
CASE WHEN A.TIPO_CALCULO_PRECO_PROMOCAO = 1
THEN A.DESCONTO_PADRAO
ELSE 0
END AS DESCONTO_PADRAO ,
A.DATA_HORA_INICIAL,
A.DATA_HORA_FINAL,
CASE WHEN A.TIPO_CALCULO_PRECO_PROMOCAO = 2
THEN A.LUCRO_BRUTO_TABELADO
ELSE 0
END AS LUCRO_BRUTO_TABELADO,
A.APENAS_CLIENTES,
A.APENAS_CONVENIOS
FROM TABELAS_DESCONTOS_SUBGRUPOS A WITH(NOLOCK)
JOIN MAIOR_TABELA_DESCONTO J WITH(NOLOCK) ON J.TABELA_DESCONTO = A.TABELA_DESCONTO
JOIN PRODUTOS Z WITH(NOLOCK) ON Z.SUBGRUPO_PRODUTO = A.SUBGRUPO_PRODUTO
LEFT
JOIN TABELAS_DESCONTOS_CLASSES E (NOLOCK) ON E.CLASSE_PRODUTO = Z.CLASSE_PRODUTO
LEFT
JOIN TABELAS_DESCONTOS_MARCAS F (NOLOCK) ON F.MARCA = Z.MARCA
LEFT
JOIN TABELAS_DESCONTOS_MARKUP G (NOLOCK) ON G.PRODUTO = Z.PRODUTO
WHERE GETDATE() BETWEEN A.DATA_HORA_INICIAL AND A.DATA_HORA_FINAL
AND ( ( DATEPART(DW, GETDATE() ) = 1 AND A.DOMINGO = 'S') OR
( DATEPART(DW, GETDATE() ) = 2 AND A.SEGUNDA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 3 AND A.TERCA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 4 AND A.QUARTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 5 AND A.QUINTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 6 AND A.SEXTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 7 AND A.SABADO = 'S') )
AND E.CLASSE_PRODUTO IS NULL
AND F.MARCA IS NULL
AND G.PRODUTO IS NULL
UNION ALL
SELECT A.TABELA_DESCONTO ,
Z.PRODUTO ,
A.TIPO_CALCULO_PRECO_PROMOCAO ,
CASE WHEN A.TIPO_CALCULO_PRECO_PROMOCAO = 1
THEN A.DESCONTO_PADRAO
ELSE 0
END AS DESCONTO_PADRAO ,
A.DATA_HORA_INICIAL,
A.DATA_HORA_FINAL,
CASE WHEN A.TIPO_CALCULO_PRECO_PROMOCAO = 2
THEN A.LUCRO_BRUTO_TABELADO
ELSE 0
END AS LUCRO_BRUTO_TABELADO,
A.APENAS_CLIENTES,
A.APENAS_CONVENIOS
FROM TABELAS_DESCONTOS_CLASSES A WITH(NOLOCK)
JOIN MAIOR_TABELA_DESCONTO J WITH(NOLOCK) ON J.TABELA_DESCONTO = A.TABELA_DESCONTO
JOIN PRODUTOS Z WITH(NOLOCK) ON Z.CLASSE_PRODUTO = A.CLASSE_PRODUTO
LEFT
JOIN TABELAS_DESCONTOS_MARCAS F (NOLOCK) ON F.MARCA = Z.MARCA
LEFT
JOIN TABELAS_DESCONTOS_MARKUP G (NOLOCK) ON G.PRODUTO = Z.PRODUTO
WHERE GETDATE() BETWEEN A.DATA_HORA_INICIAL AND A.DATA_HORA_FINAL
AND ( ( DATEPART(DW, GETDATE() ) = 1 AND A.DOMINGO = 'S') OR
( DATEPART(DW, GETDATE() ) = 2 AND A.SEGUNDA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 3 AND A.TERCA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 4 AND A.QUARTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 5 AND A.QUINTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 6 AND A.SEXTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 7 AND A.SABADO = 'S') )
AND F.MARCA IS NULL
AND G.PRODUTO IS NULL
UNION ALL
SELECT A.TABELA_DESCONTO ,
Z.PRODUTO ,
A.TIPO_CALCULO_PRECO_PROMOCAO ,
CASE WHEN A.TIPO_CALCULO_PRECO_PROMOCAO = 1
THEN A.DESCONTO_PADRAO
ELSE 0
END AS DESCONTO_PADRAO ,
A.DATA_HORA_INICIAL,
A.DATA_HORA_FINAL,
CASE WHEN A.TIPO_CALCULO_PRECO_PROMOCAO = 2
THEN A.LUCRO_BRUTO_TABELADO
ELSE 0
END AS LUCRO_BRUTO_TABELADO,
A.APENAS_CLIENTES,
A.APENAS_CONVENIOS
FROM TABELAS_DESCONTOS_MARCAS A WITH(NOLOCK)
JOIN MAIOR_TABELA_DESCONTO J WITH(NOLOCK) ON J.TABELA_DESCONTO = A.TABELA_DESCONTO
JOIN PRODUTOS Z WITH(NOLOCK) ON Z.MARCA = A.MARCA
LEFT
JOIN TABELAS_DESCONTOS_MARKUP G (NOLOCK) ON G.PRODUTO = Z.PRODUTO
WHERE GETDATE() BETWEEN A.DATA_HORA_INICIAL AND A.DATA_HORA_FINAL
AND ( ( DATEPART(DW, GETDATE() ) = 1 AND A.DOMINGO = 'S') OR
( DATEPART(DW, GETDATE() ) = 2 AND A.SEGUNDA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 3 AND A.TERCA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 4 AND A.QUARTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 5 AND A.QUINTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 6 AND A.SEXTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 7 AND A.SABADO = 'S') )
AND G.PRODUTO IS NULL
UNION ALL
SELECT A.TABELA_DESCONTO ,
Z.PRODUTO ,
A.TIPO_CALCULO_PRECO_PROMOCAO ,
CASE WHEN A.TIPO_CALCULO_PRECO_PROMOCAO = 1
THEN A.DESCONTO_PADRAO
ELSE 0
END AS DESCONTO_PADRAO ,
A.DATA_HORA_INICIAL,
A.DATA_HORA_FINAL,
CASE WHEN A.TIPO_CALCULO_PRECO_PROMOCAO = 2
THEN A.LUCRO_BRUTO_TABELADO
ELSE 0
END AS LUCRO_BRUTO_TABELADO,
A.APENAS_CLIENTES,
A.APENAS_CONVENIOS
FROM TABELAS_DESCONTOS_MARKUP A WITH(NOLOCK)
JOIN MAIOR_TABELA_DESCONTO J WITH(NOLOCK) ON J.TABELA_DESCONTO = A.TABELA_DESCONTO
JOIN PRODUTOS Z WITH(NOLOCK) ON Z.PRODUTO = A.PRODUTO
WHERE GETDATE() BETWEEN A.DATA_HORA_INICIAL AND A.DATA_HORA_FINAL
AND ( ( DATEPART(DW, GETDATE() ) = 1 AND A.DOMINGO = 'S') OR
( DATEPART(DW, GETDATE() ) = 2 AND A.SEGUNDA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 3 AND A.TERCA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 4 AND A.QUARTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 5 AND A.QUINTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 6 AND A.SEXTA_FEIRA = 'S') OR
( DATEPART(DW, GETDATE() ) = 7 AND A.SABADO = 'S') ) ```
Sem as estruturas das tabelas e os dados, é mais complicado dar uma resposta incluindo suas tabelas e colunas, então vou apresentar duas abordagens para fazer o que você precisa mas de maneira mais genérica.
Pelo que entendi, sua tabela de produto relaciona com as tabelas de descontos e você faz filtros para indicar que quando ocorre uma regra, não deve considerar a outra, então usei essas informações para montar um exemplo:
create table secao (
id int primary key,
nome varchar(50) not null);
insert into secao (id, nome) values (1, 'Seção A'), (2, 'Seção B'), (3, 'Seção C');
create table marca (
id int primary key,
nome varchar(50) not null);
insert into marca (id, nome) values (1, 'Marca A'), (2, 'Marca B');
create table produto (
id int primary key,
id_secao int,
id_marca int,
nome varchar(50) not null,
foreign key (id_secao) references secao(id),
foreign key (id_marca) references marca(id));
insert into produto (id, id_secao, id_marca, nome) values (1, 1, 1, 'Produto SA-MA 1'), (2, 1, 1, 'Produto SA-MA 2'), (3, 1, 1, 'Produto SA-MA 3'),
(4, 1, 2, 'Produto SA-MB 1'), (5, 1, 2, 'Produto SA-MB 2'), (6, 1, 2, 'Produto SA-MB 3'), (7, 1, 2, 'Produto SA-MB 4'),
(8, 2, 2, 'Produto SB-MB 1'), (9, 2, 2, 'Produto SB-MB 2'), (10, 2, 2, 'Produto SB-MB 3'),
(11, 3, 2, 'Produto SC-MB 1'), (12, 3, 1, 'Produto SC-MA 2'), (13, 3, 1, 'Produto SC-MA 3'),
(14, null, null, 'Produto S-B 1');
create table desconto_produto (
id int identity primary key,
id_produto int not null,
percentual decimal(5, 2),
foreign key (id_produto) references produto(id));
insert into desconto_produto (id_produto, percentual) values (1, 0.08), (4, 0.02), (5, 0.06), (9, 0.15), (12, 0.10);
create table desconto_secao (
id int identity primary key,
id_secao int not null,
percentual decimal(5, 2),
foreign key (id_secao) references secao(id));
insert into desconto_secao (id_secao, percentual) values (1, 0.17), (3, 0.19);
create table desconto_marca (
id int identity primary key,
id_marca int not null,
percentual decimal(5, 2),
foreign key (id_marca) references marca(id));
insert into desconto_marca (id_marca, percentual) values (2, 0.23);
Os produtos de ids 1, 4, 5, 9 e 12 tem respectivamente desconto de 0.08, 0.02, 0.06, 0.15 e 0.10, as seções de ids 1 e 3 tem 0.17 e 0.19 e finalmente a marca de id 2 tem 0.23. Com base nisso você pode fazer:
select
p.id
,p.nome nome_produto
,m.nome nome_marca
,s.nome nome_secao
,coalesce(dp.percentual, dm.percentual, ds.percentual, 0) desconto_selecionado
,dp.percentual percentual_produto
,dm.percentual percentual_marca
,ds.percentual percentual_secao
from
produto p with(nolock)
left join marca m with(nolock) on (p.id_marca = m.id)
left join secao s with(nolock) on (p.id_secao = s.id)
left join desconto_produto dp with(nolock) on (dp.id_produto = p.id)
left join desconto_marca dm with(nolock) on (dm.id_marca = p.id_marca)
left join desconto_secao ds with(nolock) on (ds.id_secao = p.id_secao);
Nessa consulta, relacionei o produto com todas as tabelas de descontos, porém para exibir o percentual de desconto, considerando a regra produto > marca > seção, usei o coalesce, o que ele faz é retornar o primeiro valor não nulo de acordo com a ordem dos parâmetros passados, que representa a regra anterior.
Uma outra maneira de priorizar algo é utilizando o rank:
with regras as (
select
p.id
,p.nome nome_produto
,m.nome nome_marca
,s.nome nome_secao
,dp.percentual
,1 prioridade
from
produto p with(nolock)
inner join desconto_produto dp with(nolock) on (dp.id_produto = p.id)
left join marca m with(nolock) on (p.id_marca = m.id)
left join secao s with(nolock) on (p.id_secao = s.id)
union all
select
p.id
,p.nome nome_produto
,m.nome nome_marca
,s.nome nome_secao
,dm.percentual
,2 prioridade
from
produto p with(nolock)
inner join desconto_marca dm with(nolock) on (dm.id_marca = p.id_marca)
left join marca m with(nolock) on (p.id_marca = m.id)
left join secao s with(nolock) on (p.id_secao = s.id)
union all
select
p.id
,p.nome nome_produto
,m.nome nome_marca
,s.nome nome_secao
,ds.percentual
,3 prioridade
from
produto p with(nolock)
inner join desconto_secao ds with(nolock) on (ds.id_secao = p.id_secao)
left join marca m with(nolock) on (p.id_marca = m.id)
left join secao s with(nolock) on (p.id_secao = s.id)
union all
select
p.id
,p.nome nome_produto
,m.nome nome_marca
,s.nome nome_secao
,cast(0.0 as decimal) percentual
,4 prioridade
from
produto p with(nolock)
left join marca m with(nolock) on (p.id_marca = m.id)
left join secao s with(nolock) on (p.id_secao = s.id)),
prioridades as (
select
id
,nome_produto
,nome_marca
,nome_secao
,percentual
,rank() over(partition by id order by prioridade) posicao
from regras)
select
id
,nome_produto
,nome_marca
,nome_secao
,percentual desconto_selecionado
from
prioridades
where
posicao = 1
Utilizei o with para utilizar resultados nomeados e ficar mais fácil de trabalhar (não é possível utilizar o rank direto na cláusula where). Em cada consulta defini uma prioridade, quanto menor a prioridade, maior é sua importância. No rank utilizei o partition by para considerar o id do produto como discriminador, pois se o mesmo produto tiver mais de um tipo de desconto, o rank vai classificar de acordo com aquele que tem a menor prioridade (o order by dentro do rank tem essa função de dizer que a menor prioridade vale mais, se fosse em ordem contrária, seria necessário utilizar desc no order by).
Ainda é necessário considerar a questão de desempenho, melhoria das consultas, porém creio que agora você tenha uma base para trabalhar com o seu problema.
Referências:
Answered by mateusalxd on December 30, 2020
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP