Query - Bring 5 higher records

Question

Query - Bring 5 higher records

Navigation

#1 by (3 votes)

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 reconciliation 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

sql sql-select sql-server query

asked by anonymous 02.08.2017 / 23:50

1 answer

What's Faster: Stack or Heap Allocation? [duplicate] MemberwiseClone of aggregates

score 3 · Accepted Answer

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