Query sorting - sql server

Asked

Viewed 271 times

0

I need to sort my query so that the column "Centername" is in accordance with the logic applied in the column "All levels" someone could help me please? 

            WITH CentroNiveis AS (
                        SELECT cen.Id, 
                               cen.IdCentroPai, 
                               cen.Nome,
                               CONVERT(VARCHAR(1000), cen.Nome) AS TodosOsNiveis, 
                               cen.IdCliente,
                               cen.Tipo,
                               1 AS Nivel, 
                               RANK() OVER (ORDER BY cen.Nome, cen.Id, cen.IdCentroPai) AS Ordem
                          FROM CentroConsolidacao cen
                         WHERE cen.IdCentroPai IS NULL
                           AND cen.Status = 'Ativo'  AND cen.IdCliente = '1'  AND cen.Tipo = 'Despesa' 

                         UNION ALL

                         SELECT cc.Id, 
                                cc.IdCentroPai, 
                                cc.Nome, 
                                CONVERT(VARCHAR(1000), cn.TodosOsNiveis + ' - ' + cc.Nome) AS TodosOsNiveis, 
                                cc.IdCliente,
                                cc.Tipo,
                                Nivel+1,
                                Ordem
                           FROM CentroConsolidacao cc
                     INNER JOIN CentroNiveis cn ON cc.IdCentroPai = cn.Id
                          WHERE cc.Status = 'Ativo'
                                 )
        SELECT 
               CEN.Id AS IdCentroConsolidacao, 
               CEN.IdCentroPai AS IdCentroPai,

               CASE WHEN CEN.Nivel = 1 THEN CEN.Nome ELSE REPLICATE('          ' , CEN.Nivel-1) + CEN.Nome END AS NomeCentroConsolidacao, 
               CEN.TodosOsNiveis,

               CEN.Nivel,

               CASE WHEN CON.Id IS NULL THEN '' ELSE CON.Id END AS IdConta,
               CASE WHEN CON.Nome IS NULL THEN '' ELSE CON.Nome END AS NomeConta ,

               SUM(ABS(rat.Credito - rat.Debito)) OVER(PARTITION BY cen.Id ORDER BY cen.Id) AS TotalCentro,
               SUM(ABS(rat.Credito - rat.Debito)) OVER(PARTITION BY con.Id ORDER BY con.Id) AS TotalConta

          FROM CentroNiveis CEN
     LEFT JOIN Conta CON ON CEN.Id = CON.IdCentroConsolidacao
     LEFT JOIN Rateio rat ON rat.IdConta = con.Id
     LEFT JOIN Lancamento lanc ON lanc.Id = rat.IdLancamento

    WHERE 1=1  
      AND lanc.Status = 'Real' 
      AND lanc.Aprovado = 'Sim'  
      AND lanc.Data >= '2015-01-01'  
      AND lanc.Data <= '2015-12-31'  
      AND lanc.IdCliente = 1  
      AND lanc.Tipo IN ( 'Lançamento' , 'Transferência' )  
      AND cen.Tipo = 'Despesa'  
      AND lanc.IdFonte IN(1)  
      AND lanc.Status = 'Real' 
      AND lanc.Aprovado = 'Sim' 

       OR CEN.Id IN (SELECT DISTINCT c1.Id AS IdCentro
                       FROM CentroConsolidacao c1 
                 INNER JOIN CentroConsolidacao c2 ON c1.Id = c2.IdCentroPai OR c2.Id = c1.IdCentroPai
                      WHERE c1.IdCliente = 1)

       OR CEN.Nivel > 1

 GROUP BY
          CEN.Id, 
          CEN.IdCentroPai, 
          CEN.TodosOsNiveis,
          CON.Id,
          CON.Nome,

          CEN.Ordem, 
          CEN.Nivel, 
          CEN.Nome,

          RAT.Credito, 
          RAT.Debito

 ORDER BY CEN.Ordem, CEN.Nivel, CEN.Nome, CON.Nome

inserir a descrição da imagem aqui

sql-server query classification

  • Does anyone have any suggestions? Any information will be useful.

  • You can put an example of what the desired output would look like?

1 answer

1

You only need to replace the following snippets of your query:

Substitute

CONVERT(VARCHAR(1000), cn.TodosOsNiveis + ' - ' + cc.Nome) AS TodosOsNiveis,

for

REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(1000), cn.TodosOsNiveis + ' - ' + cc.Nome)), 0, CHARINDEX(' - ', REVERSE(CONVERT(VARCHAR(1000), cn.TodosOsNiveis + ' - ' + cc.Nome))))) AS TodosOsNiveis,

and replace

CEN.TodosOsNiveis,

for

CASE WHEN CEN.Nivel = 1 THEN CEN.TodosOsNiveis ELSE REPLICATE('          ' , CEN.Nivel-1) + CEN.TodosOsNiveis END AS TodosOsNiveis,

Your code will look like this:

            WITH CentroNiveis AS (
                        SELECT cen.Id, 
                               cen.IdCentroPai, 
                               cen.Nome,
                               CONVERT(VARCHAR(1000), cen.Nome) AS TodosOsNiveis, 
                               cen.IdCliente,
                               cen.Tipo,
                               1 AS Nivel, 
                               RANK() OVER (ORDER BY cen.Nome, cen.Id, cen.IdCentroPai) AS Ordem
                          FROM CentroConsolidacao cen
                         WHERE cen.IdCentroPai IS NULL
                           AND cen.Status = 'Ativo'  AND cen.IdCliente = '1'  AND cen.Tipo = 'Despesa' 

                         UNION ALL

                         SELECT cc.Id, 
                                cc.IdCentroPai, 
                                cc.Nome, 
                                REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(1000), cn.TodosOsNiveis + ' - ' + cc.Nome)), 0, CHARINDEX(' - ', REVERSE(CONVERT(VARCHAR(1000), cn.TodosOsNiveis + ' - ' + cc.Nome))))) AS TodosOsNiveis, 
                                cc.IdCliente,
                                cc.Tipo,
                                Nivel+1,
                                Ordem
                           FROM CentroConsolidacao cc
                     INNER JOIN CentroNiveis cn ON cc.IdCentroPai = cn.Id
                          WHERE cc.Status = 'Ativo'
                                 )
        SELECT 
               CEN.Id AS IdCentroConsolidacao, 
               CEN.IdCentroPai AS IdCentroPai,

               CASE WHEN CEN.Nivel = 1 THEN CEN.Nome ELSE REPLICATE('          ' , CEN.Nivel-1) + CEN.Nome END AS NomeCentroConsolidacao, 
               CASE WHEN CEN.Nivel = 1 THEN CEN.TodosOsNiveis ELSE REPLICATE('          ' , CEN.Nivel-1) + CEN.TodosOsNiveis END AS TodosOsNiveis,

               CEN.Nivel,

               CASE WHEN CON.Id IS NULL THEN '' ELSE CON.Id END AS IdConta,
               CASE WHEN CON.Nome IS NULL THEN '' ELSE CON.Nome END AS NomeConta ,

               SUM(ABS(rat.Credito - rat.Debito)) OVER(PARTITION BY cen.Id ORDER BY cen.Id) AS TotalCentro,
               SUM(ABS(rat.Credito - rat.Debito)) OVER(PARTITION BY con.Id ORDER BY con.Id) AS TotalConta

          FROM CentroNiveis CEN
     LEFT JOIN Conta CON ON CEN.Id = CON.IdCentroConsolidacao
     LEFT JOIN Rateio rat ON rat.IdConta = con.Id
     LEFT JOIN Lancamento lanc ON lanc.Id = rat.IdLancamento

    WHERE 1=1  
      AND lanc.Status = 'Real' 
      AND lanc.Aprovado = 'Sim'  
      AND lanc.Data >= '2015-01-01'  
      AND lanc.Data <= '2015-12-31'  
      AND lanc.IdCliente = 1  
      AND lanc.Tipo IN ( 'Lançamento' , 'Transferência' )  
      AND cen.Tipo = 'Despesa'  
      AND lanc.IdFonte IN(1)  
      AND lanc.Status = 'Real' 
      AND lanc.Aprovado = 'Sim' 

       OR CEN.Id IN (SELECT DISTINCT c1.Id AS IdCentro
                       FROM CentroConsolidacao c1 
                 INNER JOIN CentroConsolidacao c2 ON c1.Id = c2.IdCentroPai OR c2.Id = c1.IdCentroPai
                      WHERE c1.IdCliente = 1)

       OR CEN.Nivel > 1

 GROUP BY
          CEN.Id, 
          CEN.IdCentroPai, 
          CEN.TodosOsNiveis,
          CON.Id,
          CON.Nome,

          CEN.Ordem, 
          CEN.Nivel, 
          CEN.Nome,

          RAT.Credito, 
          RAT.Debito

 ORDER BY CEN.Ordem, CEN.Nivel, CEN.Nome, CON.Nome

Browser other questions tagged sql-server query classification

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