_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....
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
Postar um comentário