Pular para o conteúdo principal

Postagem em destaque

BlackTDN :: Gerenciamento de Erros e Avisos em Projetos AdvPL/TLPP: Um Desafio Frequente no Desenvolvimento TOTVS Protheus

_Créditos da imagem: Gerada com auxílio do ChatGPT_ --- **Gerenciamento de Erros e Avisos em Projetos AdvPL/TLPP: Um Desafio Frequente no Desenvolvimento TOTVS Protheus** Ao trabalhar com projetos desenvolvidos na linguagem AdvPL/TLPP, especialmente em ambientes como o TOTVS Microsiga Protheus, desenvolvedores frequentemente se deparam com uma série de problemas e avisos de compilação. Recentemente, em uma análise de código de um dos nossos projetos, foram identificados mais de 700 problemas durante o processo de compilação, distribuídos entre erros críticos e avisos que podem afetar o desempenho e a manutenção da aplicação. ### Principais Problemas Encontrados Entre os problemas destacados, podemos citar: 1. **Erros de Nomenclatura e Duplicação de Fontes**: - Um dos erros mais críticos que surgiram foi: "Not allowed more than one source with same name". Este erro é comum quando há múltiplos arquivos PRW com o mesmo nome em diferentes pastas do projeto, o que pode

BlackTDN :: TOTVS :: Protheus :: MSSQL :: Turnover

 


Source: Github
Turnover Empresa:


IF OBJECT_ID('tempdb..##tmp_table_turnover_empresa_20230124_001', 'U') IS NOT NULL
	DROP TABLE ##tmp_table_turnover_empresa_20230124_001

BEGIN
    WITH PERIODO AS
             (
                        SELECT 
                      DISTINCT SRD.RD_DATARQ PERIODO
                          FROM SRD990 SRD
                         WHERE SRD.D_E_L_E_T_=''
                           AND SRD.RD_FILIAL<>''
                      GROUP BY SRD.RD_DATARQ
                      UNION 
                        SELECT 
                      DISTINCT LEFT(SRA.RA_ADMISSA,6) PERIODO
                         FROM SRA990 SRA
                        WHERE SRA.D_E_L_E_T_=''
                          AND SRA.RA_FILIAL<>''
                     GROUP BY LEFT(SRA.RA_ADMISSA,6)
             )
      ,TURNOVER AS (
            SELECT
                   PERIODO.PERIODO
                 , ISNULL((
                          SELECT
                                 SUM(TTRFSAI)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TTRFSAI
                                          FROM
                                                   SRE990 SRE_T
                                          WHERE
                                                   SRE_T.D_E_L_E_T_= ''
                                                   AND SRE_T.RE_EMPD='99'
                                                   AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
                                          GROUP BY
                                                   LEFT(SRE_T.RE_DATA,6)
                                                 , SRE_T.RE_EMPD
                                 )
                                 T
                   ),0)
                   TTRFSAI
                 , ISNULL((
                          SELECT
                                 SUM(TTRFENT)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TTRFENT
                                          FROM
                                                   SRE990 SRE_T
                                          WHERE
                                                   SRE_T.D_E_L_E_T_= ''
                                                   AND SRE_T.RE_EMPP='99'
                                                   AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
                                          GROUP BY
                                                   LEFT(SRE_T.RE_DATA,6)
                                                 , SRE_T.RE_EMPP
                                 )
                                 T
                   ),0)
                   TTRFENT
                 , ISNULL((
                          SELECT
                                 SUM(TFUNMES)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TFUNMES
                                          FROM
                                                   SRA990 SRA_T
                                          WHERE
                                                   SRA_T.D_E_L_E_T_= ''
                                                   AND LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
                                                   AND (
                                                            SRA_T.RA_DEMISSA=''
                                                            OR
                                                            ( 
                                                                 LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
                                                                 AND (
                                                                          LEFT(SRA_T.RA_ADMISSA,6)<=LEFT(SRA_T.RA_DEMISSA,6)
                                                                      AND LEFT(SRA_T.RA_DEMISSA,6)>=PERIODO.PERIODO
                                                                 )
                                                            )
                                                    )
                                 )
                                 T
                   ),0)
                   TFUNMES
                 , ISNULL((
                          SELECT
                                 SUM(ADMISSAO)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(SRA_A.RA_ADMISSA) AS ADMISSAO
                                            FROM SRA990 SRA_A
                                           WHERE LEFT(SRA_A.RA_ADMISSA,6)=PERIODO.PERIODO
                                        GROUP BY LEFT(SRA_A.RA_ADMISSA,6)
                                 )
                                 T
                   ),0)
                   TFUNADMMES
                 , ISNULL((
                          SELECT
                                 SUM(DEMISSAO)
                          FROM
                                 (
                                          SELECT COUNT(RA_DEMISSA) AS DEMISSAO
                                            FROM SRA990 SRA_D
                                           WHERE SRA_D.RA_DEMISSA<>''
                                             AND LEFT(SRA_D.RA_DEMISSA,6)=PERIODO.PERIODO
                                           GROUP BY LEFT(SRA_D.RA_DEMISSA,6)
                                 )
                                 T
                   ),0)
                   TFUNDEMMES
            FROM PERIODO
           WHERE PERIODO.PERIODO BETWEEN '202101' AND '202112' 
    )
    , TURNOVERT AS (
    SELECT TURNOVER.PERIODO
          ,TURNOVER.TTRFSAI
          ,TURNOVER.TTRFENT
          ,TFUNIMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNADMMES) AS FLOAT)
          ,TFUNADMMES=CAST(TURNOVER.TFUNADMMES AS FLOAT) 
          ,TFUNMES=CAST(TURNOVER.TFUNMES AS FLOAT) 
          ,TFUNDEMMES=CAST(TURNOVER.TFUNDEMMES AS FLOAT) 
          ,TFUNFMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNDEMMES) AS FLOAT) 
    FROM TURNOVER
    )
    SELECT TURNOVERT.PERIODO
          ,TURNOVERT.TTRFSAI
          ,TURNOVERT.TTRFENT
          ,TURNOVERT.TFUNIMES
          ,TURNOVERT.TFUNADMMES
          ,TURNOVERT.TFUNMES
          ,TURNOVERT.TFUNDEMMES
          ,TURNOVERT.TFUNFMES
          ,TURNOVER=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE ((TURNOVERT.TFUNADMMES+TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES) END)*100),2)
          ,TURNMOVF=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE (TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES END)*100),2)
    INTO ##tmp_table_turnover_empresa_20230124_001
    FROM TURNOVERT
END

select * from ##tmp_table_turnover_empresa_20230124_001

Turnover Filial:


IF OBJECT_ID('tempdb..##tmp_table_turnover_filial_20230124_001', 'U') IS NOT NULL
	DROP TABLE ##tmp_table_turnover_filial_20230124_001

BEGIN
    WITH PERIODO AS
             (
                        SELECT 
                      DISTINCT SRD.RD_DATARQ PERIODO
                             , SRA.RA_FILIAL
                          FROM SRD990 SRD
						  JOIN SRA990 SRA ON (SRD.RD_FILIAL=SRA.RA_FILIAL AND SRD.RD_MAT=SRA.RA_MAT)           
                         WHERE SRD.D_E_L_E_T_=''
                           AND SRA.D_E_L_E_T_=''
						   AND SRD.RD_FILIAL<>''
						   AND SRA.RA_FILIAL<>''
						   AND SRD.RD_FILIAL=SRA.RA_FILIAL
						   AND SRD.RD_MAT=SRA.RA_MAT
                      GROUP BY SRD.RD_DATARQ
                             , SRA.RA_FILIAL
                      UNION 
                        SELECT 
                      DISTINCT LEFT(SRA.RA_ADMISSA,6) PERIODO
                             , SRA.RA_FILIAL
                         FROM SRA990 SRA
                        WHERE SRA.D_E_L_E_T_=''
                          AND SRA.RA_FILIAL<>''
                     GROUP BY LEFT(SRA.RA_ADMISSA,6)
                             , SRA.RA_FILIAL
             )
      ,TURNOVER AS (
            SELECT
                   PERIODO.PERIODO
                 , PERIODO.RA_FILIAL
                 , ISNULL((
                          SELECT
                                 SUM(TTRFSAI)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TTRFSAI
                                          FROM
                                                   SRE990 SRE_T
                                          WHERE
                                                   SRE_T.D_E_L_E_T_= ''
                                                   AND SRE_T.RE_EMPD='99'
                                                   AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
												   AND SRE_T.RE_FILIALD=PERIODO.RA_FILIAL
                                          GROUP BY
                                                   LEFT(SRE_T.RE_DATA,6)
                                                 , SRE_T.RE_EMPD
												 , SRE_T.RE_FILIALD
                                 )
                                 T
                   ),0)
                   TTRFSAI
                 , ISNULL((
                          SELECT
                                 SUM(TTRFENT)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TTRFENT
                                          FROM
                                                   SRE990 SRE_T
                                          WHERE
                                                   SRE_T.D_E_L_E_T_= ''
                                                   AND SRE_T.RE_EMPP='99'
                                                   AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
												   AND SRE_T.RE_FILIALP=PERIODO.RA_FILIAL
                                          GROUP BY
                                                   LEFT(SRE_T.RE_DATA,6)
                                                 , SRE_T.RE_EMPP
												 , SRE_T.RE_FILIALP
                                 )
                                 T
                   ),0)
                   TTRFENT
                 , ISNULL((
                          SELECT
                                 SUM(TFUNMES)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TFUNMES
                                          FROM
                                                   SRA990 SRA_T
                                          WHERE
                                                   SRA_T.D_E_L_E_T_= ''
                                                   AND LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
                                                   AND (
                                                            SRA_T.RA_DEMISSA=''
                                                            OR
                                                            ( 
                                                                 LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
                                                                 AND (
                                                                          LEFT(SRA_T.RA_ADMISSA,6)<=LEFT(SRA_T.RA_DEMISSA,6)
                                                                      AND LEFT(SRA_T.RA_DEMISSA,6)>=PERIODO.PERIODO
                                                                 )
                                                            )
                                                    )
                                                   AND SRA_T.RA_FILIAL=PERIODO.RA_FILIAL
                                 )
                                 T
                   ),0)
                   TFUNMES
                 , ISNULL((
                          SELECT
                                 SUM(ADMISSAO)
                          FROM
                                 (
                                          SELECT
                                                 COUNT(SRA_A.RA_ADMISSA) AS ADMISSAO
                                            FROM SRA990 SRA_A
                                           WHERE LEFT(SRA_A.RA_ADMISSA,6)=PERIODO.PERIODO
                                             AND SRA_A.RA_FILIAL=PERIODO.RA_FILIAL
                                        GROUP BY LEFT(SRA_A.RA_ADMISSA,6)
                                                 , SRA_A.RA_FILIAL
                                 )
                                 T
                   ),0)
                   TFUNADMMES
                 , ISNULL((
                          SELECT
                                 SUM(DEMISSAO)
                          FROM
                                 (
                                          SELECT COUNT(RA_DEMISSA) AS DEMISSAO
                                            FROM SRA990 SRA_D
                                           WHERE SRA_D.RA_DEMISSA<>''
                                             AND LEFT(SRA_D.RA_DEMISSA,6)=PERIODO.PERIODO
                                             AND SRA_D.RA_FILIAL=PERIODO.RA_FILIAL
                                             AND SRA_D.RA_DEMISSA<>''
                                         GROUP BY LEFT(SRA_D.RA_DEMISSA,6)
                                                 , SRA_D.RA_FILIAL
                                 )
                                 T
                   ),0)
                   TFUNDEMMES
            FROM PERIODO
           WHERE PERIODO.PERIODO BETWEEN '202101' AND '202112'
             AND  PERIODO.RA_FILIAL BETWEEN ' ' AND 'z ' 
    )
    , TURNOVERT AS (
    SELECT TURNOVER.PERIODO
          ,TURNOVER.RA_FILIAL
          ,TURNOVER.TTRFSAI
          ,TURNOVER.TTRFENT
          ,TFUNIMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNADMMES) AS FLOAT)
          ,TFUNADMMES=CAST(TURNOVER.TFUNADMMES AS FLOAT) 
          ,TFUNMES=CAST(TURNOVER.TFUNMES AS FLOAT) 
          ,TFUNDEMMES=CAST(TURNOVER.TFUNDEMMES AS FLOAT) 
          ,TFUNFMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNDEMMES) AS FLOAT) 
    FROM TURNOVER
    )
    SELECT TURNOVERT.PERIODO
          ,TURNOVERT.RA_FILIAL
          ,TURNOVERT.TTRFSAI
          ,TURNOVERT.TTRFENT
          ,TURNOVERT.TFUNIMES
          ,TURNOVERT.TFUNADMMES
          ,TURNOVERT.TFUNMES
          ,TURNOVERT.TFUNDEMMES
          ,TURNOVERT.TFUNFMES
          ,TURNOVER=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE ((TURNOVERT.TFUNADMMES+TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES) END)*100),2)
          ,TURNMOVF=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE (TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES END)*100),2)
    INTO ##tmp_table_turnover_filial_20230124_001
    FROM TURNOVERT
END

select * from ##tmp_table_turnover_filial_20230124_001

Turnover Centro de Custo:


IF OBJECT_ID('tempdb..##tmp_table_turnover_centro_de_custo_20230124_001', 'U') IS NOT NULL
	DROP TABLE ##tmp_table_turnover_centro_de_custo_20230124_001

BEGIN
    WITH PERIODO AS
             (
                        SELECT 
                      DISTINCT SRD.RD_DATARQ PERIODO
                             , SRA.RA_FILIAL
                             , SRA.RA_CC
                          FROM SRD990 SRD
						  JOIN SRA990 SRA ON (SRD.RD_FILIAL=SRA.RA_FILIAL AND SRD.RD_MAT=SRA.RA_MAT)           
                         WHERE SRD.D_E_L_E_T_=''
                           AND SRA.D_E_L_E_T_=''
						   AND SRD.RD_FILIAL<>''
						   AND SRA.RA_FILIAL<>''
						   AND SRD.RD_FILIAL=SRA.RA_FILIAL
						   AND SRD.RD_MAT=SRA.RA_MAT
                      GROUP BY SRD.RD_DATARQ
                             , SRA.RA_FILIAL
                             , SRA.RA_CC
                      UNION 
                        SELECT 
                      DISTINCT LEFT(SRA.RA_ADMISSA,6) PERIODO
                             , SRA.RA_FILIAL
                             , SRA.RA_CC
                         FROM SRA990 SRA
                        WHERE SRA.D_E_L_E_T_=''
                          AND SRA.RA_FILIAL<>''
                     GROUP BY LEFT(SRA.RA_ADMISSA,6)
                             , SRA.RA_FILIAL
                             , SRA.RA_CC
             )
      ,TURNOVER AS (
            SELECT
                   PERIODO.PERIODO
                 , PERIODO.RA_FILIAL
                 , PERIODO.RA_CC
                 , CTT.CTT_DESC01
                 , ISNULL((
                          SELECT
                                 SUM(TTRFSAI)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TTRFSAI
                                          FROM
                                                   SRE990 SRE_T
                                          WHERE
                                                   SRE_T.D_E_L_E_T_= ''
                                                   AND SRE_T.RE_EMPD='99'
                                                   AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
												   AND SRE_T.RE_FILIALD=PERIODO.RA_FILIAL
                                                   AND SRE_T.RE_CCD=PERIODO.RA_CC
                                          GROUP BY
                                                   LEFT(SRE_T.RE_DATA,6)
                                                 , SRE_T.RE_EMPD
												 , SRE_T.RE_FILIALD
                                                 , SRE_T.RE_CCD
                                 )
                                 T
                   ),0)
                   TTRFSAI
                 , ISNULL((
                          SELECT
                                 SUM(TTRFENT)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TTRFENT
                                          FROM
                                                   SRE990 SRE_T
                                          WHERE
                                                   SRE_T.D_E_L_E_T_= ''
                                                   AND SRE_T.RE_EMPP='99'
                                                   AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
												   AND SRE_T.RE_FILIALP=PERIODO.RA_FILIAL
                                                   AND SRE_T.RE_CCP=PERIODO.RA_CC
                                          GROUP BY
                                                   LEFT(SRE_T.RE_DATA,6)
                                                 , SRE_T.RE_EMPP
												 , SRE_T.RE_FILIALP
                                                 , SRE_T.RE_CCP
                                 )
                                 T
                   ),0)
                   TTRFENT
                 , ISNULL((
                          SELECT
                                 SUM(TFUNMES)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TFUNMES
                                          FROM
                                                   SRA990 SRA_T
                                          WHERE
                                                   SRA_T.D_E_L_E_T_= ''
                                                   AND LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
                                                   AND (
                                                            SRA_T.RA_DEMISSA=''
                                                            OR
                                                            ( 
                                                                 LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
                                                                 AND (
                                                                          LEFT(SRA_T.RA_ADMISSA,6)<=LEFT(SRA_T.RA_DEMISSA,6)
                                                                      AND LEFT(SRA_T.RA_DEMISSA,6)>=PERIODO.PERIODO
                                                                 )
                                                            )
                                                    )
                                                   AND SRA_T.RA_FILIAL=PERIODO.RA_FILIAL
                                                   AND SRA_T.RA_CC=PERIODO.RA_CC
                                 )
                                 T
                   ),0)
                   TFUNMES
                 , ISNULL((
                          SELECT
                                 SUM(ADMISSAO)
                          FROM
                                 (
                                          SELECT
                                                 COUNT(SRA_A.RA_ADMISSA) AS ADMISSAO
                                            FROM SRA990 SRA_A
                                           WHERE LEFT(SRA_A.RA_ADMISSA,6)=PERIODO.PERIODO
                                             AND SRA_A.RA_FILIAL=PERIODO.RA_FILIAL
                                             AND SRA_A.RA_CC=PERIODO.RA_CC
                                        GROUP BY LEFT(SRA_A.RA_ADMISSA,6)
                                                 , SRA_A.RA_FILIAL
                                                 , SRA_A.RA_CC
                                 )
                                 T
                   ),0)
                   TFUNADMMES
                 , ISNULL((
                          SELECT
                                 SUM(DEMISSAO)
                          FROM
                                 (
                                          SELECT COUNT(RA_DEMISSA) AS DEMISSAO
                                            FROM SRA990 SRA_D
                                           WHERE SRA_D.RA_DEMISSA<>''
                                             AND LEFT(SRA_D.RA_DEMISSA,6)=PERIODO.PERIODO
                                             AND SRA_D.RA_CC=PERIODO.RA_CC
                                             AND SRA_D.RA_DEMISSA<>''
                                         GROUP BY LEFT(SRA_D.RA_DEMISSA,6)
                                                 , SRA_D.RA_FILIAL
                                                 , SRA_D.RA_CC
                                 )
                                 T
                   ),0)
                   TFUNDEMMES
            FROM PERIODO
            JOIN CTT990 CTT ON (PERIODO.RA_CC=CTT.CTT_CUSTO AND CTT.CTT_FILIAL=(CASE CTT.CTT_FILIAL WHEN ''  THEN '' ELSE PERIODO.RA_FILIAL END))
           WHERE PERIODO.PERIODO BETWEEN '202101' AND '202112' 
             AND  PERIODO.RA_FILIAL BETWEEN ' ' AND 'z ' 
             AND  PERIODO.RA_CC BETWEEN '' AND 'z' 
    )
    , TURNOVERT AS (
    SELECT TURNOVER.PERIODO
          ,TURNOVER.RA_FILIAL
          ,TURNOVER.RA_CC
          ,TURNOVER.CTT_DESC01
          ,TURNOVER.TTRFSAI
          ,TURNOVER.TTRFENT
          ,TFUNIMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNADMMES) AS FLOAT)
          ,TFUNADMMES=CAST(TURNOVER.TFUNADMMES AS FLOAT) 
          ,TFUNMES=CAST(TURNOVER.TFUNMES AS FLOAT) 
          ,TFUNDEMMES=CAST(TURNOVER.TFUNDEMMES AS FLOAT) 
          ,TFUNFMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNDEMMES) AS FLOAT) 
    FROM TURNOVER
    )
    SELECT TURNOVERT.PERIODO
          ,TURNOVERT.RA_FILIAL
          ,TURNOVERT.RA_CC
          ,TURNOVERT.CTT_DESC01
          ,TURNOVERT.TTRFSAI
          ,TURNOVERT.TTRFENT
          ,TURNOVERT.TFUNIMES
          ,TURNOVERT.TFUNADMMES
          ,TURNOVERT.TFUNMES
          ,TURNOVERT.TFUNDEMMES
          ,TURNOVERT.TFUNFMES
          ,TURNOVER=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE ((TURNOVERT.TFUNADMMES+TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES) END)*100),2)
          ,TURNMOVF=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE (TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES END)*100),2)
    INTO ##tmp_table_turnover_centro_de_custo_20230124_001
    FROM TURNOVERT
END

select * from ##tmp_table_turnover_centro_de_custo_20230124_001

Turnover Funções:


IF OBJECT_ID('tempdb..##tmp_table_turnover_funcoes_20230124_001', 'U') IS NOT NULL
	DROP TABLE ##tmp_table_turnover_funcoes_20230124_001

BEGIN
    WITH PERIODO AS
             (
                        SELECT 
                      DISTINCT SRD.RD_DATARQ PERIODO
                             , SRA.RA_FILIAL
                             , SRA.RA_CC
                             , SRA.RA_CODFUNC
                          FROM SRD990 SRD
						  JOIN SRA990 SRA ON (SRD.RD_FILIAL=SRA.RA_FILIAL AND SRD.RD_MAT=SRA.RA_MAT)           
                         WHERE SRD.D_E_L_E_T_=''
                           AND SRA.D_E_L_E_T_=''
						   AND SRD.RD_FILIAL<>''
						   AND SRA.RA_FILIAL<>''
						   AND SRD.RD_FILIAL=SRA.RA_FILIAL
						   AND SRD.RD_MAT=SRA.RA_MAT
                      GROUP BY SRD.RD_DATARQ
                             , SRA.RA_FILIAL
                             , SRA.RA_CC
                             , SRA.RA_CODFUNC
                      UNION 
                        SELECT 
                      DISTINCT LEFT(SRA.RA_ADMISSA,6) PERIODO
                             , SRA.RA_FILIAL
                             , SRA.RA_CC
                             , SRA.RA_CODFUNC
                         FROM SRA990 SRA
                        WHERE SRA.D_E_L_E_T_=''
                          AND SRA.RA_FILIAL<>''
                     GROUP BY LEFT(SRA.RA_ADMISSA,6)
                             , SRA.RA_FILIAL
                             , SRA.RA_CC
                             , SRA.RA_CODFUNC
             )
      ,TURNOVER AS (
            SELECT
                   PERIODO.PERIODO
                 , PERIODO.RA_FILIAL
                 , PERIODO.RA_CC
                 , CTT.CTT_DESC01
                 , PERIODO.RA_CODFUNC
                 , SRJ.RJ_DESC
                 , ISNULL((
                          SELECT
                                 SUM(TTRFSAI)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TTRFSAI
                                          FROM
                                                   SRE990 SRE_T
                                          WHERE
                                                   SRE_T.D_E_L_E_T_= ''
                                                   AND SRE_T.RE_EMPD='99'
                                                   AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
												   AND SRE_T.RE_FILIALD=PERIODO.RA_FILIAL
                                                   AND SRE_T.RE_CCD=PERIODO.RA_CC
                                          GROUP BY
                                                   LEFT(SRE_T.RE_DATA,6)
                                                 , SRE_T.RE_EMPD
												 , SRE_T.RE_FILIALD
                                                 , SRE_T.RE_CCD
                                 )
                                 T
                   ),0)
                   TTRFSAI
                 , ISNULL((
                          SELECT
                                 SUM(TTRFENT)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TTRFENT
                                          FROM
                                                   SRE990 SRE_T
                                          WHERE
                                                   SRE_T.D_E_L_E_T_= ''
                                                   AND SRE_T.RE_EMPP='99'
                                                   AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
												   AND SRE_T.RE_FILIALP=PERIODO.RA_FILIAL
                                                   AND SRE_T.RE_CCP=PERIODO.RA_CC
                                          GROUP BY
                                                   LEFT(SRE_T.RE_DATA,6)
                                                 , SRE_T.RE_EMPP
												 , SRE_T.RE_FILIALP
                                                 , SRE_T.RE_CCP
                                 )
                                 T
                   ),0)
                   TTRFENT
                 , ISNULL((
                          SELECT
                                 SUM(TFUNMES)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TFUNMES
                                          FROM
                                                   SRA990 SRA_T
                                          WHERE
                                                   SRA_T.D_E_L_E_T_= ''
                                                   AND LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
                                                   AND (
                                                            SRA_T.RA_DEMISSA=''
                                                            OR
                                                            ( 
                                                                 LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
                                                                 AND (
                                                                          LEFT(SRA_T.RA_ADMISSA,6)<=LEFT(SRA_T.RA_DEMISSA,6)
                                                                      AND LEFT(SRA_T.RA_DEMISSA,6)>=PERIODO.PERIODO
                                                                 )
                                                            )
                                                    )
                                                   AND SRA_T.RA_FILIAL=PERIODO.RA_FILIAL
                                                   AND SRA_T.RA_CC=PERIODO.RA_CC
                                                   AND SRA_T.RA_CODFUNC=PERIODO.RA_CODFUNC
                                 )
                                 T
                   ),0)
                   TFUNMES
                 , ISNULL((
                          SELECT
                                 SUM(ADMISSAO)
                          FROM
                                 (
                                          SELECT
                                                 COUNT(SRA_A.RA_ADMISSA) AS ADMISSAO
                                            FROM SRA990 SRA_A
                                           WHERE LEFT(SRA_A.RA_ADMISSA,6)=PERIODO.PERIODO
                                             AND SRA_A.RA_FILIAL=PERIODO.RA_FILIAL
                                             AND SRA_A.RA_CC=PERIODO.RA_CC
                                             AND SRA_A.RA_CODFUNC=PERIODO.RA_CODFUNC
                                        GROUP BY LEFT(SRA_A.RA_ADMISSA,6)
                                                 , SRA_A.RA_FILIAL
                                                 , SRA_A.RA_CC
                                                 , SRA_A.RA_CODFUNC
                                 )
                                 T
                   ),0)
                   TFUNADMMES
                 , ISNULL((
                          SELECT
                                 SUM(DEMISSAO)
                          FROM
                                 (
                                          SELECT COUNT(RA_DEMISSA) AS DEMISSAO
                                            FROM SRA990 SRA_D
                                           WHERE SRA_D.RA_DEMISSA<>''
                                             AND LEFT(SRA_D.RA_DEMISSA,6)=PERIODO.PERIODO
                                             AND SRA_D.RA_CC=PERIODO.RA_CC
                                             AND SRA_D.RA_CODFUNC=PERIODO.RA_CODFUNC
                                             AND SRA_D.RA_DEMISSA<>''
                                         GROUP BY LEFT(SRA_D.RA_DEMISSA,6)
                                                 , SRA_D.RA_FILIAL
                                                 , SRA_D.RA_CC
                                                 , SRA_D.RA_CODFUNC
                                 )
                                 T
                   ),0)
                   TFUNDEMMES
            FROM PERIODO
            JOIN CTT990 CTT ON (PERIODO.RA_CC=CTT.CTT_CUSTO AND CTT.CTT_FILIAL=(CASE CTT.CTT_FILIAL WHEN ''  THEN '' ELSE PERIODO.RA_FILIAL END))
            JOIN SRJ990 SRJ ON (PERIODO.RA_CODFUNC=SRJ.RJ_FUNCAO AND SRJ.RJ_FILIAL=(CASE SRJ.RJ_FILIAL WHEN ''  THEN '' ELSE PERIODO.RA_FILIAL END))
           WHERE PERIODO.PERIODO BETWEEN '202101' AND '202112'
             AND  PERIODO.RA_FILIAL BETWEEN '' AND 'z' 
             AND  PERIODO.RA_CC BETWEEN '' AND 'z ' 
             AND  PERIODO.RA_CODFUNC BETWEEN '' AND 'z'
    )
    , TURNOVERT AS (
    SELECT TURNOVER.PERIODO
          ,TURNOVER.RA_FILIAL
          ,TURNOVER.RA_CC
          ,TURNOVER.CTT_DESC01
          ,TURNOVER.RA_CODFUNC
          ,TURNOVER.RJ_DESC
          ,TURNOVER.TTRFSAI
          ,TURNOVER.TTRFENT
          ,TFUNIMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNADMMES) AS FLOAT)
          ,TFUNADMMES=CAST(TURNOVER.TFUNADMMES AS FLOAT) 
          ,TFUNMES=CAST(TURNOVER.TFUNMES AS FLOAT) 
          ,TFUNDEMMES=CAST(TURNOVER.TFUNDEMMES AS FLOAT) 
          ,TFUNFMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNDEMMES) AS FLOAT) 
    FROM TURNOVER
    )
    SELECT TURNOVERT.PERIODO
          ,TURNOVERT.RA_FILIAL
          ,TURNOVERT.RA_CC
          ,TURNOVERT.CTT_DESC01
          ,TURNOVERT.RA_CODFUNC
          ,TURNOVERT.RJ_DESC
          ,TURNOVERT.TTRFSAI
          ,TURNOVERT.TTRFENT
          ,TURNOVERT.TFUNIMES
          ,TURNOVERT.TFUNADMMES
          ,TURNOVERT.TFUNMES
          ,TURNOVERT.TFUNDEMMES
          ,TURNOVERT.TFUNFMES
          ,TURNOVER=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE ((TURNOVERT.TFUNADMMES+TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES) END)*100),2)
          ,TURNMOVF=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE (TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES END)*100),2)
    INTO ##tmp_table_turnover_funcoes_20230124_001
    FROM TURNOVERT
END

select * from ##tmp_table_turnover_funcoes_20230124_001

Turnover Funcionários:


IF OBJECT_ID('tempdb..##tmp_table_turnover_funcionarios_20230124_001', 'U') IS NOT NULL
	DROP TABLE ##tmp_table_turnover_funcionarios_20230124_001

BEGIN
    WITH PERIODO AS
             (
                        SELECT 
                      DISTINCT SRD.RD_DATARQ PERIODO
                             , SRA.RA_FILIAL
                             , SRA.RA_CC
                             , SRA.RA_CODFUNC
                             , SRA.RA_MAT
                             , SRA.R_E_C_N_O_ SRARECNO
                          FROM SRD990 SRD
						  JOIN SRA990 SRA ON (SRD.RD_FILIAL=SRA.RA_FILIAL AND SRD.RD_MAT=SRA.RA_MAT)           
                         WHERE SRD.D_E_L_E_T_=''
                           AND SRA.D_E_L_E_T_=''
						   AND SRD.RD_FILIAL<>''
						   AND SRA.RA_FILIAL<>''
						   AND SRD.RD_FILIAL=SRA.RA_FILIAL
						   AND SRD.RD_MAT=SRA.RA_MAT
                      GROUP BY SRD.RD_DATARQ
                             , SRA.RA_FILIAL
                             , SRA.RA_CC
                             , SRA.RA_CODFUNC
                             , SRA.RA_MAT
                             , SRA.R_E_C_N_O_
                      UNION 
                        SELECT 
                      DISTINCT LEFT(SRA.RA_ADMISSA,6) PERIODO
                             , SRA.RA_FILIAL
                             , SRA.RA_CC
                             , SRA.RA_CODFUNC
                             , SRA.RA_MAT
                             , SRA.R_E_C_N_O_ SRARECNO
                         FROM SRA990 SRA
                        WHERE SRA.D_E_L_E_T_=''
                          AND SRA.RA_FILIAL<>''
                     GROUP BY LEFT(SRA.RA_ADMISSA,6)
                             , SRA.RA_FILIAL
                             , SRA.RA_CC
                             , SRA.RA_CODFUNC
                             , SRA.RA_MAT
                             , SRA.R_E_C_N_O_
             )
      ,TURNOVER AS (
            SELECT
                   PERIODO.PERIODO
                 , PERIODO.RA_FILIAL
                 , PERIODO.RA_CC
                 , CTT.CTT_DESC01
                 , PERIODO.RA_CODFUNC
                 , SRJ.RJ_DESC
                 , PERIODO.RA_MAT
                 , PERIODO.SRARECNO
                 , ISNULL((
                          SELECT
                                 SUM(TTRFSAI)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TTRFSAI
                                          FROM
                                                   SRE990 SRE_T
                                          WHERE
                                                   SRE_T.D_E_L_E_T_= ''
                                                   AND SRE_T.RE_EMPD='99'
                                                   AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
												   AND SRE_T.RE_FILIALD=PERIODO.RA_FILIAL
                                                   AND SRE_T.RE_CCD=PERIODO.RA_CC
                                                   AND SRE_T.RE_MATD=PERIODO.RA_MAT
                                          GROUP BY
                                                   LEFT(SRE_T.RE_DATA,6)
                                                 , SRE_T.RE_EMPD
												 , SRE_T.RE_FILIALD
                                                 , SRE_T.RE_CCD
                                                 , SRE_T.RE_MATD
                                 )
                                 T
                   ),0)
                   TTRFSAI
                 , ISNULL((
                          SELECT
                                 SUM(TTRFENT)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TTRFENT
                                          FROM
                                                   SRE990 SRE_T
                                          WHERE
                                                   SRE_T.D_E_L_E_T_= ''
                                                   AND SRE_T.RE_EMPP='99'
                                                   AND LEFT(SRE_T.RE_DATA,6)=PERIODO.PERIODO
												   AND SRE_T.RE_FILIALP=PERIODO.RA_FILIAL
                                                   AND SRE_T.RE_CCP=PERIODO.RA_CC
                                                   AND SRE_T.RE_MATP=PERIODO.RA_MAT
                                          GROUP BY
                                                   LEFT(SRE_T.RE_DATA,6)
                                                 , SRE_T.RE_EMPP
												 , SRE_T.RE_FILIALP
                                                 , SRE_T.RE_CCP
                                                 , SRE_T.RE_MATP
                                 )
                                 T
                   ),0)
                   TTRFENT
                 , ISNULL((
                          SELECT
                                 SUM(TFUNMES)
                          FROM
                                 (
                                          SELECT
                                                   COUNT(1) AS TFUNMES
                                          FROM
                                                   SRA990 SRA_T
                                          WHERE
                                                   SRA_T.D_E_L_E_T_= ''
                                                   AND LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
                                                   AND (
                                                            SRA_T.RA_DEMISSA=''
                                                            OR
                                                            ( 
                                                                 LEFT(SRA_T.RA_ADMISSA,6)<=PERIODO.PERIODO
                                                                 AND (
                                                                          LEFT(SRA_T.RA_ADMISSA,6)<=LEFT(SRA_T.RA_DEMISSA,6)
                                                                      AND LEFT(SRA_T.RA_DEMISSA,6)>=PERIODO.PERIODO
                                                                 )
                                                            )
                                                    )
                                                   AND SRA_T.RA_FILIAL=PERIODO.RA_FILIAL
                                                   AND SRA_T.RA_CC=PERIODO.RA_CC
                                                   AND SRA_T.RA_CODFUNC=PERIODO.RA_CODFUNC
                                                   AND SRA_T.RA_MAT=PERIODO.RA_MAT
                                                   AND SRA_T.R_E_C_N_O_=PERIODO.SRARECNO
                                 )
                                 T
                   ),0)
                   TFUNMES
                 , ISNULL((
                          SELECT
                                 SUM(ADMISSAO)
                          FROM
                                 (
                                          SELECT
                                                 COUNT(SRA_A.RA_ADMISSA) AS ADMISSAO
                                            FROM SRA990 SRA_A
                                           WHERE LEFT(SRA_A.RA_ADMISSA,6)=PERIODO.PERIODO
                                             AND SRA_A.RA_FILIAL=PERIODO.RA_FILIAL
                                             AND SRA_A.RA_CC=PERIODO.RA_CC
                                             AND SRA_A.RA_CODFUNC=PERIODO.RA_CODFUNC
                                             AND SRA_A.RA_MAT=PERIODO.RA_MAT
                                        GROUP BY LEFT(SRA_A.RA_ADMISSA,6)
                                                 , SRA_A.RA_FILIAL
                                                 , SRA_A.RA_CC
                                                 , SRA_A.RA_CODFUNC
                                                 , SRA_A.RA_MAT
                                 )
                                 T
                   ),0)
                   TFUNADMMES
                 , ISNULL((
                          SELECT
                                 SUM(DEMISSAO)
                          FROM
                                 (
                                          SELECT COUNT(RA_DEMISSA) AS DEMISSAO
                                            FROM SRA990 SRA_D
                                           WHERE SRA_D.RA_DEMISSA<>''
                                             AND LEFT(SRA_D.RA_DEMISSA,6)=PERIODO.PERIODO
                                             AND SRA_D.RA_CC=PERIODO.RA_CC
                                             AND SRA_D.RA_CODFUNC=PERIODO.RA_CODFUNC
                                             AND SRA_D.RA_MAT=PERIODO.RA_MAT
                                             AND SRA_D.RA_DEMISSA<>''
                                             AND SRA_D.R_E_C_N_O_=PERIODO.SRARECNO
                                         GROUP BY LEFT(SRA_D.RA_DEMISSA,6)
                                                 , SRA_D.RA_FILIAL
                                                 , SRA_D.RA_CC
                                                 , SRA_D.RA_CODFUNC
                                                 , SRA_D.RA_MAT
                                 )
                                 T
                   ),0)
                   TFUNDEMMES
            FROM PERIODO
            JOIN CTT990 CTT ON (PERIODO.RA_CC=CTT.CTT_CUSTO AND CTT.CTT_FILIAL=(CASE CTT.CTT_FILIAL WHEN ''  THEN '' ELSE PERIODO.RA_FILIAL END))
            JOIN SRJ990 SRJ ON (PERIODO.RA_CODFUNC=SRJ.RJ_FUNCAO AND SRJ.RJ_FILIAL=(CASE SRJ.RJ_FILIAL WHEN ''  THEN '' ELSE PERIODO.RA_FILIAL END))
           WHERE PERIODO.PERIODO BETWEEN '202101 ' AND '202112 ' 
             AND  PERIODO.RA_FILIAL BETWEEN ' ' AND 'z ' 
             AND  PERIODO.RA_CC BETWEEN ' ' AND 'z ' 
             AND  PERIODO.RA_CODFUNC BETWEEN '' AND 'z'
    )
    , TURNOVERT AS (
    SELECT TURNOVER.PERIODO
          ,TURNOVER.RA_FILIAL
          ,TURNOVER.RA_CC
          ,TURNOVER.CTT_DESC01
          ,TURNOVER.RA_CODFUNC
          ,TURNOVER.RJ_DESC
          ,TURNOVER.RA_MAT
          ,TURNOVER.SRARECNO          
          ,TURNOVER.TTRFSAI
          ,TURNOVER.TTRFENT
          ,TFUNIMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNADMMES) AS FLOAT)
          ,TFUNADMMES=CAST(TURNOVER.TFUNADMMES AS FLOAT) 
          ,TFUNMES=CAST(TURNOVER.TFUNMES AS FLOAT) 
          ,TFUNDEMMES=CAST(TURNOVER.TFUNDEMMES AS FLOAT) 
          ,TFUNFMES=CAST((TURNOVER.TFUNMES-TURNOVER.TFUNDEMMES) AS FLOAT) 
    FROM TURNOVER
    )
    SELECT TURNOVERT.PERIODO
          ,TURNOVERT.RA_FILIAL
          ,TURNOVERT.RA_CC
          ,TURNOVERT.CTT_DESC01
          ,TURNOVERT.RA_CODFUNC
          ,TURNOVERT.RJ_DESC
          ,TURNOVERT.RA_MAT
          ,TURNOVERT.SRARECNO          
          ,TURNOVERT.TTRFSAI
          ,TURNOVERT.TTRFENT
          ,TURNOVERT.TFUNIMES
          ,TURNOVERT.TFUNADMMES
          ,TURNOVERT.TFUNMES
          ,TURNOVERT.TFUNDEMMES
          ,TURNOVERT.TFUNFMES
          ,TURNOVER=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE ((TURNOVERT.TFUNADMMES+TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES) END)*100),2)
          ,TURNMOVF=ROUND(((CASE TFUNIMES WHEN 0 THEN 0 ELSE (TURNOVERT.TFUNDEMMES)/2/TURNOVERT.TFUNIMES END)*100),2)
    INTO ##tmp_table_turnover_funcionarios_20230124_001
    FROM TURNOVERT
END

select * from ##tmp_table_turnover_funcionarios_20230124_001

Comentários

Postagens mais visitadas