none
LEFT JOIN com retorno do primeiro registro se não houver retorno RRS feed

  • Pergunta

  • Bom dia pessoal,

    Tenho uma dúvida que é meio difícil de explicar, mas vou tentar, vamos lá!

    Tenho uma procedure que executa uma série de left joins

    	SELECT
    		do.id_Distribuidor
    		,do.ch_NomeFantasia
    		,MatrizPDV.id_SAP AS id_SAP
    		,SUM(vl_Faturado) AS vl_Faturado
    		,dobc.id_BonusCategoria
    		,ISNULL(bc.num_Bonus,0) AS num_BonusMkt
    		,ISNULL(bc2.num_Bonus,0) AS num_BonusInv
    		,ISNULL(bc.num_Bonus,0) + ISNULL(bc2.num_Bonus,0) AS num_BonusTotal
    		,ISNULL(pmkt.TotalGasto_Personalizado,0) AS TotalGasto_Personalizado
    		,ISNULL(pmkt.TotalGasto_Padrao,0) AS TotalGasto_Padrao
    		,ISNULL(pmkt.TotalGasto_Investimento,0) AS TotalGasto_Investimento
    
    	FROM
    		vw_Distribuidores do
    
    	JOIN tb_PontoVenda pv ON pv.id_Distribuidor = do.id_Distribuidor
    	LEFT JOIN tb_FaturamentoAcumulado fat ON fat.id_PontoVenda = pv.id_PontoVenda
    											AND fat.num_AnoReferencia = @AnoReferencia
    											AND fat.num_MesReferencia = @UltimoMesFaturamento--MONTH(GETDATE())
    											AND fat.num_AnoPiramide = @AnoReferencia
    
    	LEFT JOIN tb_Distribuidores_BonusCategoria dobc ON dobc.id_Distribuidor = do.id_Distribuidor
    												AND dobc.num_AnoReferencia = @AnoReferencia
    												AND dobc.num_SemestreReferencia = @SemestreReferencia
    
    	LEFT JOIN tb_BonusConfigurador bc		   ON bc.id_BonusCategoria = dobc.id_BonusCategoria
    												AND bc.num_AnoReferencia = @AnoReferencia
    												AND bc.id_BonusTipo = 1 /* MARKETING */
    
    	LEFT JOIN tb_BonusConfigurador bc2		   ON bc2.id_BonusCategoria = dobc.id_BonusCategoria
    												AND bc2.num_AnoReferencia = @AnoReferencia
    												AND bc2.id_BonusTipo = 8 /* INVESTIMENTO */

    Acontece que agora em 2017, nessas tabelas em que executo o LEFT JOIN não há registo ainda para este ano, portanto tenho como resultante alguns "NULL". Até ai tudo bem!

    Eu gostaria de fazer algo que se a resultante for NULL, trazer por exemplo o último registro inserido naquela tabela que atende as condições com exceção do ano.

    Será que fazer um OUTER APPLY para pegar o último registro, e depois lá no SELECT usar um COALESCE no campo é a melhor solução?

    Muito obrigado pela ajuda!

    terça-feira, 10 de janeiro de 2017 11:03

Respostas

  • Sergio,

    Bom dia!

    Caso complicado mesmo. Pelo que percebo, provavelmente você terá que dividir essa consulta. Uma solução seria fazer o Left Join sem o critério do ano, salvar a tabela de retorno, e depois identificar somente os casos que entram no seu critério, algo como isso:

    /* Tabelas de Exemplo      */

    Create Table #Principal

    (

           Cod          Int Identity

           ,Ano   Int   

           ,Nome  VarChar(100)

    )

     

    Insert Into #Principal

    Values

            (2017, 'João')

           ,(2017, 'Melissa')

           ,(2017, 'Marina')

     

     

    Create Table #Left

    (

           CodPrincipal Int

           ,Ano                Int   

           ,Setor              Int

    )

     

    Insert Into #Left

    Values

            (1, 2016, 1)

           ,(1, 2016, 2)

           ,(2, 2017, 1)

           ,(2, 2016, 2)

           ,(3, 2017, 1)

           ,(3, 2017, 2)

     

     

    /*Consulta   */

    Selectp.Cod, p.Nome, p.Ano

                 ,l.CodPrincipal, l.Setor, l.Ano AnoLeft

                 ,Row_Number() Over(Partition By Cod, p.Ano Order By Cod) Ordem    

                 ,Iif(p.Ano = l.Ano, 1, 0) Aux --Verificando se há o ano correto

    Into   #Aux

    From   #Principal p

                 Left Join #Left l

                        On     p.Cod = l.CodPrincipal

     

     

    /* Deletando os registros inválidos, deixando uma ocorrência caso o critério do ano não seja atendido */

    Delete a

    From   (

                        Select *

                                      ,Max(Aux) Over(Partition By Cod, Ano) AnoMaximo

                        From   #Aux

                 )a

    Where  (Ano <> AnoLeft And AnoMaximo > 0)      -- Quando há pelo menos um registro do ano (2017 no caso)

                 Or (AnoMaximo = 0 And Ordem > 1)  -- Quando não nenhum registros do ano. Selecionar o "último válido"

     

    Select*

    From   #Aux

     

    Se você usar o Outer Apply, você ainda terá que filtrar os registros, no final das contas seria a mesma solução, só que com mais dados para filtrar.


    att,
    Mailson Santana - DBA
    MCSE: Data Management and Analytics
    Site: www.mailsonsantana.com.br
    Linkedin: mailsonsantana





    terça-feira, 10 de janeiro de 2017 11:56

Todas as Respostas

  • Sergio,

    Bom dia!

    Caso complicado mesmo. Pelo que percebo, provavelmente você terá que dividir essa consulta. Uma solução seria fazer o Left Join sem o critério do ano, salvar a tabela de retorno, e depois identificar somente os casos que entram no seu critério, algo como isso:

    /* Tabelas de Exemplo      */

    Create Table #Principal

    (

           Cod          Int Identity

           ,Ano   Int   

           ,Nome  VarChar(100)

    )

     

    Insert Into #Principal

    Values

            (2017, 'João')

           ,(2017, 'Melissa')

           ,(2017, 'Marina')

     

     

    Create Table #Left

    (

           CodPrincipal Int

           ,Ano                Int   

           ,Setor              Int

    )

     

    Insert Into #Left

    Values

            (1, 2016, 1)

           ,(1, 2016, 2)

           ,(2, 2017, 1)

           ,(2, 2016, 2)

           ,(3, 2017, 1)

           ,(3, 2017, 2)

     

     

    /*Consulta   */

    Selectp.Cod, p.Nome, p.Ano

                 ,l.CodPrincipal, l.Setor, l.Ano AnoLeft

                 ,Row_Number() Over(Partition By Cod, p.Ano Order By Cod) Ordem    

                 ,Iif(p.Ano = l.Ano, 1, 0) Aux --Verificando se há o ano correto

    Into   #Aux

    From   #Principal p

                 Left Join #Left l

                        On     p.Cod = l.CodPrincipal

     

     

    /* Deletando os registros inválidos, deixando uma ocorrência caso o critério do ano não seja atendido */

    Delete a

    From   (

                        Select *

                                      ,Max(Aux) Over(Partition By Cod, Ano) AnoMaximo

                        From   #Aux

                 )a

    Where  (Ano <> AnoLeft And AnoMaximo > 0)      -- Quando há pelo menos um registro do ano (2017 no caso)

                 Or (AnoMaximo = 0 And Ordem > 1)  -- Quando não nenhum registros do ano. Selecionar o "último válido"

     

    Select*

    From   #Aux

     

    Se você usar o Outer Apply, você ainda terá que filtrar os registros, no final das contas seria a mesma solução, só que com mais dados para filtrar.


    att,
    Mailson Santana - DBA
    MCSE: Data Management and Analytics
    Site: www.mailsonsantana.com.br
    Linkedin: mailsonsantana





    terça-feira, 10 de janeiro de 2017 11:56
  • Deleted
    terça-feira, 10 de janeiro de 2017 20:43
  • Sergio,

    Existe alguma tabela em seu ambiente que contenha esta estrutura de anos ou até mesmo um calendário?


    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]

    quarta-feira, 11 de janeiro de 2017 12:59