Query - Bring 5 major records

Asked

Viewed 140 times

3

I have the query below, where I need to bring only the 2 largest columns that have the highest values. Today is coming out like this:

Conciliacao     Extrato         Integração
150               140               80

That is, I wanted to bring only conciliation and extract that currently are the columns that have the largest quantities.

select
count(distinct FACB.ChamadoID) [Qtde_Conciliacao Bancária],
count(distinct FACV.ChamadoID) [Qtde_Conciliacao de Vendas],
count(distinct FAEF.ChamadoID) [Qtde_Extrato Financeiro],

count(distinct FAI.ChamadoID) [Qtde_Integração],
count(distinct FAR.ChamadoID) [Qtde_Recebíveis],
count(distinct FAAB.ChamadoID) [Qtde_Arquivos de Baixa],

count(distinct FASC.ChamadoID) [Qtde_Serviços e Cadastro],
count(distinct FABGT.ChamadoID) [Qtde_Bugs-Relatórios e Telas]
from Solicitacao S
left join Usuario U

on U.UsuID = S.UsuIDResponsavel
left join FRM_6 FACB
on FACB.ChamadoID = S.SolID and FACB.C08 = 3236 -- Quantidade de Chamados Forma de Atuacao Conciliação Bancária

left join FRM_6 FACV
on FACV.ChamadoID = S.SolID and FACV.C08 = 3237 -- Quantidade de Chamados Forma de Atuacao Conciliação de Vendas
left join FRM_6 FAEF
on FAEF.ChamadoID = S.SolID and FAEF.C08 = 3238 -- Quantidade de Chamados Forma 
de Atuacao Extrato Financeiro
left join FRM_6 FAI
on FAI.ChamadoID = S.SolID and FAI.C08 = 3239 -- Quantidade de Chamados Forma de Atuacao Integração

left join FRM_6 FAR
on FAR.ChamadoID = S.SolID and FAR.C08 = 3240 -- Quantidade de Chamados Forma de Atuacao Recebíveis

left join FRM_6 FAAB
on FAAB.ChamadoID = S.SolID and FAAB.C08 = 3241 -- Quantidade de Chamados Forma de Atuacao Arquivo de Baixa
left join FRM_6 FASC
on FASC.ChamadoID = S.SolID and FASC.C08 = 3300 -- Quantidade de Chamados Forma de Atuacao Serviços e Cadastros
left join FRM_6 FABGT

on FABGT.ChamadoID = S.SolID and FABGT.C08 = 3301 
-- Quantidade de Chamados Forma de Atuacao 8 - Bugs - Relatórios e Telas

where U.UsuIDGrupo = 1151 and S.ProID in(2,5,12) and S.SolStatus = 9 --S.SolDataFechamento between @Data_Inicial and @Data_Final 
  • What does "larger records" mean? // It is not necessary to have multiple junctions of FRM_6. With a single one you can get the same result.

  • José Diniz, had several Join’s because of the structure of our bank. What I want to bring is only the columns that have the largest records.

  • If the merge between the Request and User tables is of the LEFT JOIN type, then you should be careful when selecting columns from the User table in the WHERE clause.

1 answer

3


Evaluate the following code.

-- código #1 v3
with
cteConsulta as (
SELECT count(distinct case when F.C08 = 3236 then F.ChamadoID end) as CB,
       count(distinct case when F.C08 = 3237 then F.ChamadoID end) as CV,
       count(distinct case when F.C08 = 3238 then F.ChamadoID end) as EF,
       count(distinct case when F.C08 = 3239 then F.ChamadoID end) as I,
       count(distinct case when F.C08 = 3240 then F.ChamadoID end) as R,
       count(distinct case when F.C08 = 3241 then F.ChamadoID end) as AB,
       count(distinct case when F.C08 = 3300 then F.ChamadoID end) as SC,
       count(distinct case when F.C08 = 3301 then F.ChamadoID end) as BGT
  from Solicitacao as S
       left join Usuario as U on U.UsuID = S.UsuIDResponsavel
       left join FRM_6 as F on F.ChamadoID = S.SolID 
  where U.UsuIDGrupo = 1151
        and S.ProID in (2, 5, 12) 
        and S.SolStatus = 9 
        and (F.ChamadoID is null or F.C08 in (3236, 3237, 3238, 3239, 3240, 3241, 3300, 3301))
),
cteUnpivot as (
SELECT DenoColuna, ValorColuna
  from cteConsulta
       unpivot (ValorColuna for DenoColuna in ([CB], [CV], [EF], [I], [R], [AB], [SC], [BGT])) as U
)
SELECT top (2) DenoColuna, ValorColuna
  from cteUnpivot
  order by ValorColuna desc;
  • 1

    Good Joseph, you are a master. I am learning a lot from you

Browser other questions tagged

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