Ordination Plan of Accounts

Asked

Viewed 49 times

0

Currently this ordained

3.1
3.10
3.11
3.2
3.3
3.4
...
3.9

and should stay:

3.1
3.2
3.3
3.4
...
3.9
3.10
3.11

script:

SELECT GCD_Tid,
       GCD_Ordem,
               GCD_Nome,
               GCD_GCD_TidGrupoContasDre, 
               [1], 
               [2], 
               [3], 
               [4], 
               [5], 
               [6], 
               [7], 
               [8], 
               [9], 
               [10], 
               [11], 
               [12]
        FROM
        (
            SELECT mes, 
                   valor, 
                   GCD_Tid, 
                   GCD_Ordem, 
                   GCD_Nome, 
                   GCD_GCD_TidGrupoContasDre
            FROM GRUPO_CONTAS_DRE GCD
                 LEFT JOIN PLANO_CONTAS PCT ON GCD.GCD_Tid = PCT.PCT_GCD_TidGrupoContasDre
                 OUTER APPLY
            (
                SELECT SUM(CASE
                               WHEN CPR_TipoConta = 'P'
                               THEN ISNULL(CPR_ValorAReceber, 0) * -1
                               ELSE ISNULL(CPR_ValorAReceber, 0)
                           END) AS valor, 
                       DATEPART(MONTH, cpr.CPR_DataVencimento) AS mes
                FROM CONTAS_PAGARRECEBER CPR(READUNCOMMITTED)
                WHERE PCT_Tid = cpr.CPR_PCT_TidPlanoContas
                      AND YEAR(cpr.CPR_DataVencimento) = 2020
                GROUP BY CPR_DataVencimento
            ) cpr
        ) AS contas PIVOT(SUM(valor) FOR mes IN([1], 
                                                [2], 
                                                [3], 
                                                [4], 
                                                [5], 
                                                [6], 
                                                [7], 
                                                [8], 
                                                [9], 
                                                [10], 
                                                [11], 
                                                [12])) AS PivotTable
        ORDER BY RTRIM(LTRIM(GCD_Ordem))

I tried many ways and could not, could give me a light?

sql-server

  • You want to sort by integer numbers, but you are ordering strings.

  • If I make ordering by integer numbers it stays the same way, I would like it to make ordering before the first point and so on.

  • It is impossible for a numerical sorting to be in the same way as a sorting as text for the given example. Maybe you are not converting your account code to numeric properly. Ex. 2.3.15.7 must be converted to 02003015007 and then ordered.

  • Could you give me an example of what I could do?

  • One solution is to create a sort column in the table , laborious but the sql gets "clean"

1 answer

0

I got it this way:

CREATE PROCEDURE [dbo].[FINANCEIRO_CONSULTA_DRE]    
(@pEmpresa [INT],     
 @pAno     [INT]    
)    
AS    
    BEGIN    
  
 create table #tmp (GCD_Tid int, GCD_Ordem varchar(10), GCD_Nome varchar(150), GCD_GCD_TidGrupoContasDre int,   
            [1] numeric(12,2),     
               [2] numeric(12,2),     
               [3] numeric(12,2),     
               [4] numeric(12,2),     
               [5] numeric(12,2),     
               [6] numeric(12,2),     
               [7] numeric(12,2),     
               [8] numeric(12,2),     
               [9] numeric(12,2),     
               [10] numeric(12,2),     
               [11] numeric(12,2),     
               [12] numeric(12,2),  
      [ORDEM_1]  INT,  
      [ORDEM_2]  INT,  
      [ORDEM_3]  INT,  
      [ORDEM_4]  INT,  
      [ORDEM_5]  INT,  
      [ORDEM_6]  INT  
 )  
  
 INSERT INTO #tmp  
  
        SELECT  GCD_Tid,     
               GCD_Ordem,     
               GCD_Nome,     
               GCD_GCD_TidGrupoContasDre,     
               [1],     
               [2],     
               [3],     
               [4],     
               [5],     
               [6],     
               [7],     
               [8],     
               [9],     
               [10],     
               [11],     
               [12],  
      null,    
      null,  
      null,  
      null,  
      null,  
      null  
        FROM    
        (    
            SELECT mes,     
                   valor,     
                   GCD_Tid,     
                   GCD_Ordem,     
                   GCD_Nome,     
                   GCD_GCD_TidGrupoContasDre    
            FROM GRUPO_CONTAS_DRE GCD    
                 LEFT JOIN PLANO_CONTAS PCT ON GCD.GCD_Tid = PCT.PCT_GCD_TidGrupoContasDre    
                 OUTER APPLY    
            (    
                SELECT SUM(CASE    
                               WHEN CPR_TipoConta = 'P'    
                               THEN ISNULL(CPR_ValorAReceber, 0) * -1    
                               ELSE ISNULL(CPR_ValorAReceber, 0)    
                           END) AS valor,     
                       DATEPART(MONTH, cpr.CPR_DataVencimento) AS mes    
                FROM CONTAS_PAGARRECEBER CPR(READUNCOMMITTED)    
                WHERE PCT_Tid = cpr.CPR_PCT_TidPlanoContas    
                      AND (cpr.CPR_EMP_TidEmpresaCedente = @pEmpresa    
                           OR @pEmpresa IS NULL)    
                      AND YEAR(cpr.CPR_DataVencimento) = @pAno    
                GROUP BY CPR_DataVencimento    
            ) cpr    
        ) AS contas PIVOT(SUM(valor) FOR mes IN([1],     
                                                [2],     
                                                [3],     
                                                [4],     
                                                [5],     
                                                [6],     
                                                [7],     
                                                [8],     
                                                [9],     
                                                [10],     
                                                [11],     
                                                [12])) AS PivotTable    
        --ORDER BY CONVERT(INT, LEFT(GCD_Ordem, (CASE    
        --                                           WHEN GCD_Ordem NOT LIKE '%.%'    
        --                                           THEN GCD_Ordem    
        --                                           ELSE CHARINDEX('.', GCD_Ordem)    
        --                                       END) - 1))    
      --ORDER BY RTRIM(LTRIM(GCD_Ordem))    
  
  
   DECLARE CUR CURSOR KEYSET  
  
   FOR SELECT DISTINCT GCD_Tid, GCD_Nome, GCD_Ordem FROM #tmp (READUNCOMMITTED)  
  
   DECLARE @GCD_Tid  INT;  
   DECLARE @GCD_Nome  VARCHAR(150);  
   DECLARE @GCD_Ordem VARCHAR(10);  
  
   CREATE TABLE #TMP2 ([COLUMN_ID] INT, [VALUE] INT)  
 OPEN CUR;  
   
 FETCH NEXT FROM CUR INTO @GCD_Tid, @GCD_Nome, @GCD_Ordem  
 WHILE(@@fetch_status <> -1)  
 BEGIN  
   DECLARE CUR_ORDEM CURSOR KEYSET  
  
   FOR SELECT [COLUMN_ID], [VALUE] FROM dbo.[fn_split_string_to_column](@GCD_Ordem, '.')   
  
   DECLARE @COLUMN_ID INT;  
   DECLARE @VALUE  VARCHAR(10);  
  
  
   OPEN CUR_ORDEM;  
     
   FETCH NEXT FROM CUR_ORDEM INTO @COLUMN_ID, @VALUE  
   WHILE(@@fetch_status <> -1)  
   BEGIN  
       
    IF(@COLUMN_ID = 1)  
    BEGIN  
     UPDATE #tmp SET [ORDEM_1] = @VALUE  WHERE GCD_Tid = @GCD_Tid  
    END  
    IF(@COLUMN_ID = 2)  
    BEGIN  
     UPDATE #tmp SET [ORDEM_2] = CAST(@VALUE AS int) WHERE GCD_Tid = @GCD_Tid  
    END  
    IF(@COLUMN_ID = 3)  
    BEGIN  
     UPDATE #tmp SET [ORDEM_3] = CAST(@VALUE AS int) WHERE GCD_Tid = @GCD_Tid  
    END  
    IF(@COLUMN_ID = 4)  
    BEGIN  
     UPDATE #tmp SET [ORDEM_4] = CAST(@VALUE AS int) WHERE GCD_Tid = @GCD_Tid  
    END  
    IF(@COLUMN_ID = 5)  
    BEGIN  
     UPDATE #tmp SET [ORDEM_5] = CAST(@VALUE AS int) WHERE GCD_Tid = @GCD_Tid  
    END  
    IF(@COLUMN_ID = 6)  
    BEGIN  
     UPDATE #tmp SET [ORDEM_6] = CAST(@VALUE AS int) WHERE GCD_Tid = @GCD_Tid  
    END  
        
    FETCH NEXT FROM CUR_ORDEM INTO @COLUMN_ID, @VALUE  
   END;  
  
   CLOSE CUR_ORDEM;  
   DEALLOCATE CUR_ORDEM;  
  
     FETCH NEXT FROM CUR INTO @GCD_Tid, @GCD_Nome, @GCD_Ordem  
 END;  
  
 CLOSE CUR;  
 DEALLOCATE CUR;  
   
 SELECT   
  
  GCD_Tid,     
        GCD_Ordem,     
        GCD_Nome,     
        GCD_GCD_TidGrupoContasDre,     
        [1],     
        [2],     
        [3],     
        [4],     
        [5],     
        [6],     
        [7],     
        [8],     
        [9],     
        [10],     
        [11],     
        [12]  
 FROM #tmp DRE   
 order by ORDEM_1,ORDEM_2,ORDEM_3,ORDEM_4,ORDEM_5,ORDEM_6  
  
 drop table  #tmp  
  
    END;

Browser other questions tagged sql-server

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