Result Hierarchy prioritization in a view (Largest Record and Order specifies within the largest record)

Question

I am putting together a structure within a view where I am working with market levels (retail segment). Following this idea I have a product that can contain more than one current registration; it can contain, for example, section A with 10% discount and mark B with 15% and in a product level line an X product with 8% discount%.

I need the Return to be if I select Product X the discount value is 8% (even if within this product contains Section A, and Mark B).

When I select a product of the brand B must return 15% (since it is not registered in the same registration in the "tab" of products and independent if it is registered in the tab of Sections).

When I select a product from Section A, this product cannot be "within" brand B nor within the other ratings.

There are other rules within the View that are quieter are not able to resolve this point above. Recalling that I always need to consider the largest registered Current record among the chosen product, and the lowest parameterized market level.

Prioritization is left to right:

Product / Brand / Classes / Subgroup / Group / Sections

Follow view for those who agree to give a touch:

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

  

Answer 1

Without the table structures and the data, it is more complicated to give an answer including your tables and columns, so I will present two approaches to do what you need but more generally.

From what I understand, your product table relates to the discount tables and you make filters to indicate that when one rule occurs, you should not consider the other, so I used this information to build an example:

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);

The ids 1, 4, 5, 9 and 12 products have respectively discount of 0.08, 0.02, 0.06, 0.15 and 0.10, the ids 1 and 3 sections have 0.17 and 0.19 and finally the id 2 brand has 0.23. Based on this you can do:

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);

In this consultation, I related the product to all discount tables, but to display the percentage of discount, considering the product rule > brand > section, I used the coalesce, what it does is return the first non-zero value according to the order of the parameters passed, which represents the previous rule.

Another way to prioritize something is by using the 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

I used the with to use named results and make it easier to work (cannot use rank right in the clause where). In each query I defined a priority, the lower the priority, the greater its importance. In the rank I used the partition by to consider the product id as a discriminator, as if the same product has more than one type of discount, the rank will rank according to the one who has the lowest priority (the order by within the rank has the function of saying that the lowest priority is worth more, if it were in the opposite order, it would be necessary to use desc in the order by).

It is still necessary to consider the issue of performance, improvement of consultations, but I believe that now you have a basis to work with your problem.

References:

COALESCE (Transact-SQL)

RANK (Transact-SQL)

WITH common_table_expression (Transact-SQL)