Select with several Left Joins

Question

Select with several Left Joins

Navigation

#1 by (1 votes)

0

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

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

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

asked by anonymous 06.07.2017 / 22:17

1 answer

Result Web Service RETROFIT in Another Activity - Android How to give permissions to user groups in PostgreSQL?

score 1 · Answer 1

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

Exemplifying the standardization process, a line containing phone numbers

2732220987 27997820001 2740632020

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

2732220987     
27997820001 
2740632020

You must supplement the code by replacing colunas with the column names and demais filtros with the constraint conditions to be placed in each WHERE. 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;