Select with multiple Left Joins

Asked

Viewed 328 times

0

I performed a LEFT JOIN considering as attributes (CPF, TEL1, TEL2 and TEL3). Why all these? Why the field CPF often returns zeroed in one of the bases, so I’m considering the phone fields as well.

As code below, I need to know if there is a simpler way to perform this crossing.

inserir a descrição da imagem aqui

SELECT
convert(date,A.DT_EMISSAO) as DT_EMISSAO
,A.EPS
,A.MAILING
,A.CPF_TITULAR
,A.DDD_TERMINAL
,A.TELEFONE_TERMINAL
,A.PRODUTO
,A.VERSAO
,A.CANAL_VENDAS
,COUNT(*) AS QTDE_REG
,LEFT(C.TELEFONE,2) + REPLICATE('0',9 - LEN(RIGHT(C.TELEFONE,8))) + RTRIM (RIGHT(C.TELEFONE,8)) AS TELEFONE
,C.NOME_ARQUIVO
,C.DATA_BASE
,L.STATUS_FV

FROM [10.125.172.152].[televendas].[txt].[FichaDeVendasConsolidada] A with (nolock)

LEFT JOIN Carga_Outbound C ON (CONVERT(VARCHAR,A.DDD_TERMINAL) + CONVERT (VARCHAR, A.TELEFONE_TERMINAL) = LEFT(C.TELEFONE,2) + REPLICATE('0',9 - LEN(RIGHT(C.TELEFONE,8))) + RTRIM (RIGHT(C.TELEFONE,8)))
OR
CONVERT(VARCHAR,A.DDD_TERMINAL) + CONVERT (VARCHAR, A.TELEFONE_TERMINAL) = C.TELEFONE

LEFT JOIN De_ParaVersao L ON A.VERSAO = L.VERSAO_FV

WHERE 
       (A.VERSAO not like '%MAPFRE%'
    AND A.VERSAO not like '%MAFRE%'
    AND A.VERSAO not like '%SVA%'
    AND A.VERSAO not like '%PRODUTO%'
    AND A.VERSAO NOT LIKE '%ACEITOU%'
    AND A.VERSAO NOT LIKE '%NAO ACEITOU%'
    AND A.VERSAO NOT LIKE '%Ponto Adicional%'
    AND A.VERSAO NOT LIKE '%SEGURO%')
    AND A.CANAL_VENDAS = 'OUTBOUND'
    AND A.MAILING = '3420'
    AND C.DATA_BASE IS NOT NULL

    AND CONVERT(DATE,A.DT_EMISSAO) between 
    (select CONVERT(DATE,dateadd(dd,-(day(DATEADD(MONTH,-2, getdate()))-1),DATEADD(MONTH,-2, getdate()))))
    AND
    (select CONVERT(DATE,dateadd(s,-1,dateadd(mm,datediff(m,0,DATEADD(MONTH,0, getdate()))+1,0))))

GROUP BY
    A.DT_EMISSAO,A.EPS,A.MAILING,A.CPF_TITULAR,A.DDD_TERMINAL,A.TELEFONE_TERMINAL,A.PRODUTO,A.VERSAO,A.CANAL_VENDAS,C.TELEFONE,C.NOME_ARQUIVO,C.DATA_BASE,L.STATUS_FV

ORDER BY
    A.DT_EMISSAO,COUNT(*) DESC, C.DATA_BASE

---------------------------------------------------------
----:UNION CPF:-----
---------------------------------------------------------

 UNION ALL

  SELECT 
convert(date,B.DT_EMISSAO) as DT_EMISSAO
,B.EPS
,B.MAILING
,B.CPF_TITULAR
,B.DDD_TERMINAL
,B.TELEFONE_TERMINAL
,B.PRODUTO
,B.VERSAO
,B.CANAL_VENDAS
,COUNT(*) AS QTDE_REG
,LEFT(D.TELEFONE,2) + REPLICATE('0',9 - LEN(RIGHT(D.TELEFONE,8))) + RTRIM (RIGHT(D.TELEFONE,8)) AS TELEFONE
,D.NOME_ARQUIVO
,D.DATA_BASE
,M.STATUS_FV


FROM (
SELECT
convert(date,A.DT_EMISSAO) as DT_EMISSAO
,A.EPS
,A.MAILING
,A.CPF_TITULAR
,A.DDD_TERMINAL
,A.TELEFONE_TERMINAL
,A.PRODUTO
,A.VERSAO
,A.CANAL_VENDAS
,COUNT(*) AS QTDE_REG
,LEFT(C.TELEFONE,2) + REPLICATE('0',9 - LEN(RIGHT(C.TELEFONE,8))) + RTRIM (RIGHT(C.TELEFONE,8)) AS TELEFONE
,C.NOME_ARQUIVO
,C.DATA_BASE
,L.STATUS_FV


FROM [10.125.172.152].[televendas].[txt].[FichaDeVendasConsolidada] A with (nolock)

LEFT JOIN Carga_Outbound C ON (CONVERT(VARCHAR,A.DDD_TERMINAL) + CONVERT (VARCHAR, A.TELEFONE_TERMINAL) = LEFT(C.TELEFONE,2) + REPLICATE('0',9 - LEN(RIGHT(C.TELEFONE,8))) + RTRIM (RIGHT(C.TELEFONE,8)))
OR
CONVERT(VARCHAR,A.DDD_TERMINAL) + CONVERT (VARCHAR, A.TELEFONE_TERMINAL) = C.TELEFONE
LEFT JOIN De_ParaVersao L ON A.VERSAO = L.VERSAO_FV

WHERE 

       (A.VERSAO not like '%MAPFRE%'
    AND A.VERSAO not like '%MAFRE%'
    AND A.VERSAO not like '%SVA%'
    AND A.VERSAO not like '%PRODUTO%'
    AND A.VERSAO NOT LIKE '%ACEITOU%'
    AND A.VERSAO NOT LIKE '%NAO ACEITOU%'
    AND A.VERSAO NOT LIKE '%Ponto Adicional%'
    AND A.VERSAO NOT LIKE '%SEGURO%')
    AND A.CANAL_VENDAS = 'OUTBOUND'
    AND A.MAILING = '3420'
    AND C.DATA_BASE IS NULL

    AND CONVERT(DATE,A.DT_EMISSAO) between 
    (select CONVERT(DATE,dateadd(dd,-(day(DATEADD(MONTH,-2, getdate()))-1),DATEADD(MONTH,-2, getdate()))))
    AND
    (select CONVERT(DATE,dateadd(s,-1,dateadd(mm,datediff(m,0,DATEADD(MONTH,0, getdate()))+1,0))))

GROUP BY
    A.DT_EMISSAO,A.EPS,A.MAILING,A.CPF_TITULAR,A.DDD_TERMINAL,A.TELEFONE_TERMINAL,A.PRODUTO,A.VERSAO,A.CANAL_VENDAS,C.TELEFONE,C.NOME_ARQUIVO,C.DATA_BASE,L.STATUS_FV

) B

LEFT JOIN Carga_Outbound D ON B.CPF_TITULAR = D.NR_CPF

LEFT JOIN De_ParaVersao M ON B.VERSAO = M.VERSAO_FV

WHERE 

       (B.VERSAO not like '%MAPFRE%'
    AND B.VERSAO not like '%MAFRE%'
    AND B.VERSAO not like '%SVA%'
    AND B.VERSAO not like '%PRODUTO%'
    AND B.VERSAO NOT LIKE '%ACEITOU%'
    AND B.VERSAO NOT LIKE '%NAO ACEITOU%'
    AND B.VERSAO NOT LIKE '%Ponto Adicional%'
    AND B.VERSAO NOT LIKE '%SEGURO%')
    AND B.CANAL_VENDAS = 'OUTBOUND'
    AND B.MAILING = '3420'
    AND D.DATA_BASE IS NOT NULL

    AND CONVERT(DATE,B.DT_EMISSAO) between 
    (select CONVERT(DATE,dateadd(dd,-(day(DATEADD(MONTH,-2, getdate()))-1),DATEADD(MONTH,-2, getdate()))))
    AND
    (select CONVERT(DATE,dateadd(s,-1,dateadd(mm,datediff(m,0,DATEADD(MONTH,0, getdate()))+1,0))))

GROUP BY
    B.DT_EMISSAO,B.EPS,B.MAILING,B.CPF_TITULAR,B.DDD_TERMINAL,B.TELEFONE_TERMINAL,B.PRODUTO,B.VERSAO,B.CANAL_VENDAS,D.TELEFONE,D.NOME_ARQUIVO,D.DATA_BASE,M.STATUS_FV

ORDER BY
    B.DT_EMISSAO,COUNT(*) DESC, D.DATA_BASE

---------------------------------------------------------
----:UNION TELEFONE 1:-----
---------------------------------------------------------

UNION ALL

SELECT 
 convert(date,E.DT_EMISSAO) as DT_EMISSAO
,E.EPS
,E.MAILING
,E.CPF_TITULAR
,E.DDD_1
,E.TELEFONE_CONTATO_1
,E.PRODUTO
,E.VERSAO
,E.CANAL_VENDAS
,COUNT(*) AS QTDE_REG
,LEFT(F.TELEFONE,2) + REPLICATE('0',9 - LEN(RIGHT(F.TELEFONE,8))) + RTRIM (RIGHT(F.TELEFONE,8)) AS TELEFONE
,F.NOME_ARQUIVO
,F.DATA_BASE
,N.STATUS_FV


FROM (
  SELECT 
convert(date,B.DT_EMISSAO) as DT_EMISSAO
,B.EPS
,B.MAILING
,B.CPF_TITULAR
,B.DDD_TERMINAL
,B.TELEFONE_TERMINAL
,B.PRODUTO
,B.VERSAO
,B.CANAL_VENDAS
,COUNT(*) AS QTDE_REG
,LEFT(D.TELEFONE,2) + REPLICATE('0',9 - LEN(RIGHT(D.TELEFONE,8))) + RTRIM (RIGHT(D.TELEFONE,8)) AS TELEFONE
,D.NOME_ARQUIVO
,D.DATA_BASE
,M.STATUS_FV


FROM (
SELECT
convert(date,A.DT_EMISSAO) as DT_EMISSAO
,A.EPS
,A.MAILING
,A.CPF_TITULAR
,A.DDD_TERMINAL
,A.TELEFONE_TERMINAL
,A.PRODUTO
,A.VERSAO
,A.CANAL_VENDAS
,COUNT(*) AS QTDE_REG
,LEFT(C.TELEFONE,2) + REPLICATE('0',9 - LEN(RIGHT(C.TELEFONE,8))) + RTRIM (RIGHT(C.TELEFONE,8)) AS TELEFONE
,C.NOME_ARQUIVO
,C.DATA_BASE
,L.STATUS_FV


FROM [10.125.172.152].[televendas].[txt].[FichaDeVendasConsolidada] A with (nolock)

LEFT JOIN Carga_Outbound C ON (CONVERT(VARCHAR,A.DDD_TERMINAL) + CONVERT (VARCHAR, A.TELEFONE_TERMINAL) = LEFT(C.TELEFONE,2) + REPLICATE('0',9 - LEN(RIGHT(C.TELEFONE,8))) + RTRIM (RIGHT(C.TELEFONE,8)))
OR
CONVERT(VARCHAR,A.DDD_TERMINAL) + CONVERT (VARCHAR, A.TELEFONE_TERMINAL) = C.TELEFONE
LEFT JOIN De_ParaVersao L ON A.VERSAO = L.VERSAO_FV

WHERE 

       (A.VERSAO not like '%MAPFRE%'
    AND A.VERSAO not like '%MAFRE%'
    AND A.VERSAO not like '%SVA%'
    AND A.VERSAO not like '%PRODUTO%'
    AND A.VERSAO NOT LIKE '%ACEITOU%'
    AND A.VERSAO NOT LIKE '%NAO ACEITOU%'
    AND A.VERSAO NOT LIKE '%Ponto Adicional%'
    AND A.VERSAO NOT LIKE '%SEGURO%')
    AND A.CANAL_VENDAS = 'OUTBOUND'
    AND A.MAILING = '3420'
    AND C.DATA_BASE IS NULL

    AND CONVERT(DATE,A.DT_EMISSAO) between 
    (select CONVERT(DATE,dateadd(dd,-(day(DATEADD(MONTH,-2, getdate()))-1),DATEADD(MONTH,-2, getdate()))))
    AND
    (select CONVERT(DATE,dateadd(s,-1,dateadd(mm,datediff(m,0,DATEADD(MONTH,0, getdate()))+1,0))))

GROUP BY
    A.DT_EMISSAO,A.EPS,A.MAILING,A.CPF_TITULAR,A.DDD_TERMINAL,A.TELEFONE_TERMINAL,A.PRODUTO,A.VERSAO,A.CANAL_VENDAS,C.TELEFONE,C.NOME_ARQUIVO,C.DATA_BASE,L.STATUS_FV

) B

LEFT JOIN Carga_Outbound D ON B.CPF_TITULAR = D.NR_CPF

LEFT JOIN De_ParaVersao M ON B.VERSAO = M.VERSAO_FV

WHERE 

       (B.VERSAO not like '%MAPFRE%'
    AND B.VERSAO not like '%MAFRE%'
    AND B.VERSAO not like '%SVA%'
    AND B.VERSAO not like '%PRODUTO%'
    AND B.VERSAO NOT LIKE '%ACEITOU%'
    AND B.VERSAO NOT LIKE '%NAO ACEITOU%'
    AND B.VERSAO NOT LIKE '%Ponto Adicional%'
    AND B.VERSAO NOT LIKE '%SEGURO%')
    AND B.CANAL_VENDAS = 'OUTBOUND'
    AND B.MAILING = '3420'
    AND D.DATA_BASE IS NULL

    AND CONVERT(DATE,B.DT_EMISSAO) between 
    (select CONVERT(DATE,dateadd(dd,-(day(DATEADD(MONTH,-2, getdate()))-1),DATEADD(MONTH,-2, getdate()))))
    AND
    (select CONVERT(DATE,dateadd(s,-1,dateadd(mm,datediff(m,0,DATEADD(MONTH,0, getdate()))+1,0))))

GROUP BY
    B.DT_EMISSAO,B.EPS,B.MAILING,B.CPF_TITULAR,B.DDD_TERMINAL,B.TELEFONE_TERMINAL,B.PRODUTO,B.VERSAO,B.CANAL_VENDAS,D.TELEFONE,D.NOME_ARQUIVO,D.DATA_BASE,M.STATUS_FV

) E

LEFT JOIN Carga_Outbound F ON (CONVERT(VARCHAR,E.DDD_1) + CONVERT (VARCHAR,E.TELEFONE_CONTATO_1) = LEFT(F.TELEFONE,2) + REPLICATE('0',9 - LEN(RIGHT(F.TELEFONE,8))) + RTRIM (RIGHT(F.TELEFONE,8)))
OR
CONVERT(VARCHAR,E.DDD_1) + CONVERT (VARCHAR,E.TELEFONE_CONTATO_1) = F.TELEFONE

LEFT JOIN De_ParaVersao N ON E.VERSAO = N.VERSAO_FV

WHERE 

       (E.VERSAO not like '%MAPFRE%'
    AND E.VERSAO not like '%MAFRE%'
    AND E.VERSAO not like '%SVA%'
    AND E.VERSAO not like '%PRODUTO%'
    AND E.VERSAO NOT LIKE '%ACEITOU%'
    AND E.VERSAO NOT LIKE '%NAO ACEITOU%'
    AND E.VERSAO NOT LIKE '%Ponto Adicional%'
    AND E.VERSAO NOT LIKE '%SEGURO%')
    AND E.CANAL_VENDAS = 'OUTBOUND'
    AND E.MAILING = '3420'
    AND F.DATA_BASE IS NOT NULL

    AND CONVERT(DATE,E.DT_EMISSAO) between 
    (select CONVERT(DATE,dateadd(dd,-(day(DATEADD(MONTH,-2, getdate()))-1),DATEADD(MONTH,-2, getdate()))))
    AND
    (select CONVERT(DATE,dateadd(s,-1,dateadd(mm,datediff(m,0,DATEADD(MONTH,0, getdate()))+1,0))))

GROUP BY
    E.DT_EMISSAO,E.EPS,E.MAILING,E.CPF_TITULAR,E.DDD_1,E.TELEFONE_CONTATO_1,E.PRODUTO,E.VERSAO,E.CANAL_VENDAS,F.TELEFONE,F.NOME_ARQUIVO,F.DATA_BASE,N.STATUS_FV

ORDER BY
    E.DT_EMISSAO,COUNT(*) DESC, F.DATA_BASE
sql sql-server
  • 1

    You need to make your question more objective. Try to explain what this load table does? Which attributes between the two tables have a relationship ?

  • To consider a phone seems to be a gambit. Leaving that aside, you want the combination to work with the junction of Cpf or, null case on return, with any combination of the three return phones with any of the three charge phones?

  • That’s right, Jefferson...really, in the ideal world, would be the CPF, but it’s not the reality, I don’t have the CPF on all return bases.

  • Rafael, the attributes of the table, is in the image above. Cargo = mailing (Customer base for a call center to perform sales). Return = Base of sales made in mailing. Attributes between tables = (CPF, TEL 1, TEL 2 and TEL 3). Why these 4? Inside the tbl_Return comes often with the CPF reset. So I decided to perform the crossing by phones.

  • I don’t understand :-(

  • @Edilainemartins: In each of the tables there are individual columns for TEL1, TEL2 and TEL3? If so, what are the names of these columns? In the code only appears TELEFONE_TERMINAL (sales) and TELEPHONE (charge). // The crossing should be by comparing the set {TEL1, TEL2, TEL3} of the two tables OR should be individually, as in a Cartesian product (TEL1 sales with TEL1 load, then TEL2 load after TEL3 load; TEL2 sales with TEL1 load, then TEL2 load after TEL3 load etc) ?

  • @Edilainemartins: It would be useful if you added in the topic description, for each table, the name of the columns directly involved, how they are declared, what it contains etc. // Why is it necessary to add '0' in the phone number, when it has 8 digits? // Does each table have a column that acts as a primary key? // What is the approximate number of rows in each table? // The columns with phone, in the load table, are always correctly filled, without extra spaces?

Show 2 more comments

1 answer

1

Edilaine, here is code draft that considers that, no CPF in one of the bases, the crossing is performed phone to phone. For this it was necessary to transform each row of each table into 3 rows, one for each phone. It is the process of normalization, through the operator UNPIVOT.

Exemplifying the normalization process, a line containing the telephone numbers

2732220987 27997820001 2740632020

is transformed into 3 lines, each with a single phone number:

2732220987     
27997820001 
2740632020

You must supplement the code by replacing colunas by the names of the columns and demais filtros by the conditions of restriction to be placed in each WHERE cup. The code was structured in CTE, in order to simplify development and facilitate understanding and maintenance.

-- código #1 v5
-- gera o período de emissão
declare @dataInicial date, @dataFinal date;
set @dataInicial= CONVERT(DATE,dateadd(dd,-(day(DATEADD(MONTH,-2, getdate()))-1),DATEADD(MONTH,-2, getdate())));
set @dataFinal= CONVERT(DATE,dateadd(s,-1,dateadd(mm,datediff(m,0,DATEADD(MONTH,0, getdate()))+1,0)));

--
with 
-- padroniza colunas de telefone de carga
transfCarga as (
SELECT NR_CPF, TELEFONE1, TELEFONE2, TELEFONE3, DATA_BASE, NOME_ARQUIVO,
       case len(TELEFONE1)
            when 11 then TELEFONE1
            when 10 then substring(TELEFONE1, 1, 2) + '0' + substring(TELEFONE1, 3, 8)
       end as DDDTelefone1,
       case len(TELEFONE2)
            when 11 then TELEFONE2
            when 10 then substring(TELEFONE2, 1, 2) + '0' + substring(TELEFONE2, 3, 8)
       end as DDDTelefone2,
       case len(TELEFONE3)
            when 11 then TELEFONE3
            when 10 then substring(TELEFONE3, 1, 2) + '0' + substring(TELEFONE3, 3, 8)
       end as DDDTelefone3
  from Carga_Outbound
  where 1=1 --demais filtros de Carga
),

-- padroniza colunas de telefone de venda
transfVenda as (
SELECT CPF_TITULAR, DDD_1, TELEFONE_1, DDD_2, TELEFONE_2, DDD_3, TELEFONE_3, DT_EMISSAO, 
       PRODUTO, VERSAO, CANAL_VENDAS, MAILING, EPS,
       case len(TELEFONE_1)
            when 9 then convert(char(2), DDD_1) + convert(char(9), TELEFONE_1)
            when 8 then convert(char(2), DDD_1) + '0' + convert(char(8), TELEFONE_1)
       end as DDDTelefone1,
       case len(TELEFONE_2)
            when 9 then convert(char(2), DDD_2) + convert(char(9), TELEFONE_2)
            when 8 then convert(char(2), DDD_2) + '0' + convert(char(8), TELEFONE_2)
       end as DDDTelefone2,
       case len(TELEFONE_3)
            when 9 then convert(char(2), DDD_3) + convert(char(9), TELEFONE_3)
            when 8 then convert(char(2), DDD_3) + '0' + convert(char(8), TELEFONE_3)
       end as DDDTelefone3
  from FichaDeVendasConsolidada
  where cast(DT_EMISSAO as date) between @dataInicial and @dataFinal
        and 1=1 --demais filtros de Vendas
),
-- normaliza colunas de telefone da cte transfCarga
transfCargaN as (
SELECT NR_CPF, TELEFONE1, TELEFONE2, TELEFONE3, DATA_BASE, NOME_ARQUIVO, DDDTelefone
  from transfCarga
       unpivot (DDDTelefone for Origem in (DDDTelefone1, DDDTelefone2, DDDTelefone3)) as U
),

-- normaliza colunas de telefone da cte transfVenda
transfVendaN as (
SELECT CPF_TITULAR, DDD_1, TELEFONE_1, DDD_2, TELEFONE_2, DDD_3, TELEFONE_3, DT_EMISSAO, 
       PRODUTO, VERSAO, CANAL_VENDAS, MAILING, EPS, DDDTelefone
  from transfVenda
       unpivot (DDDTelefone for Origem in (DDDTelefone1, DDDTelefone2, DDDTelefone3)) as U
),

-- seleciona casos em que há CPF nas duas tabelas
retornoCPF as (
SELECT V.CPF_TITULAR as CPF, 
       V.DDD_1, V.TELEFONE_1, V.DDD_2, V.TELEFONE_2, V.DDD_3, V.TELEFONE_3, 
       V.DT_EMISSAO, V.PRODUTO, V.VERSAO, V.CANAL_VENDAS, V.MAILING, V.EPS,
       C.TELEFONE1, C.TELEFONE2, C.TELEFONE3, C.DATA_BASE, C.NOME_ARQUIVO
  from FichaDeVendasConsolidada as V 
       inner join Carga_Outbound as C on V.CPF_TITULAR = C.NR_CPF
  where C.NR_CPF is not null
        and V.CPF_TITULAR is not null
),

-- seleciona casos em que não há CPF nas duas tabelas
retornoTEL as (
SELECT coalesce(VN.CPF_TITULAR, CN.NR_CPF) as CPF, 
       VN.DDD_1, VN.TELEFONE_1, VN.DDD_2, VN.TELEFONE_2, VN.DDD_3, VN.TELEFONE_3, 
       VN.DT_EMISSAO, VN.PRODUTO, VN.VERSAO, VN.CANAL_VENDAS, VN.MAILING, VN.EPS,
       CN.TELEFONE1, CN.TELEFONE2, CN.TELEFONE3, CN.DATA_BASE, CN.NOME_ARQUIVO
  from transfVendaN as VN
       inner join transfCargaN as CN on VN.DDDTelefone = CN.DDDTelefone 
  where (VN.CPF_TITULAR is null or CN.NR_CPF is null)
        and VN.DDDTelefone is not null
        and CN.DDDTelefone is not null
)
-- junta tudo, eliminando linhas repetidas
SELECT *, 'C' as Origem
  from retornoCPF
union
SELECT *, 'T'
  from retornoTEL;

  • Hi, @José Diz ... thank you so much for your help. Actually I did not complete the code, since it got huge and I put only the left realized with tel 1 (terminal phone) and Cpf as an example. On the CPF, I add a zero to the left of the phone field, because one of the bases for crossing comes with zero to the left and another not, so I perform this procedure. Thanks for the tips on the description of the topics. abs.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.