TransWikia.com

Priorização em Hierarquia de resultado em uma view (Maior Registro e Ordem especifica dentro do maior registro)

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') ) ```              

  

One Answer

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:

COALESCE (Transact-SQL)

RANK (Transact-SQL)

WITH common_table_expression (Transact-SQL)

Answered by mateusalxd on December 30, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP