Pular para o conteúdo principal

Postagem em destaque

BlackTDN :: LeetCode :: Comparando Implementações Harbour e TLPP para o Desafio Longest Palindromic Substring

_Créditos das imagens: ChatGPT_ ### LeetCode :: Comparando Implementações Harbour e TLPP para o Desafio Longest Palindromic Substring Resolver o problema do [Longest Palindromic Substring](https://leetcode.com/problems/longest-palindromic-substring/description/) é um exercício clássico de programação, que desafia desenvolvedores a encontrar a maior substring palindrômica dentro de uma string. Recentemente, exploramos soluções tanto em Harbour quanto em TLPP (Total Language Protheus Programming). Neste artigo, comparamos as implementações nessas duas linguagens, destacando suas semelhanças, diferenças e funcionalidades específicas. #### Implementações em Harbour ##### Versão 5.1 Essa solução utiliza a técnica de expansão a partir do centro do palíndromo. Cada caractere ou par de caracteres consecutivos é considerado um possível "centro". O algoritmo expande em ambas as direções enquanto os caracteres forem iguais, retornando o maior palíndromo encontrado. ##### Versão 5....

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