2018-08-06 - AD TOTVS RM - Buscando férias e licenças.sql

SELECT 
        VA.CODAVALIACAO         AS CODAVALIACAO,
        VA.NOME                 AS NOME, 
        VP.CODPARTICIPANTE      AS CODPARTICIPANTE,
        FE.CHAPA                   AS CHAPAAVALIADO, 
       'Férias'                 AS TIPO,  
       DATAINICIO               AS DTINICIAL, 
       DATAFIM                  AS DTFINAL, 
       VA.DATAABERTURA          AS DTABERTURA,
       'S'                      AS LICENCA,  
       FC.CODPESSOA             AS CODPESSOA
 FROM PFUFERIASPER AS FE
 INNER JOIN VADPARTICIPANTES AS VP
 ON VP.CHAPAAVALIADO = FE.CHAPA
 INNER JOIN VADAVALIACAO AS VA
 ON VA.CODCOLIGADA = VP.CODCOLIGADA
 AND VA.CODAVALIACAO = VP.CODAVALIACAO
 INNER JOIN PFUNC AS FC
 ON FE.CHAPA = FC.CHAPA
 where DATAINICIO > '2018-01-01' and VA.CODAVALIACAO = 55
 AND MONTH(VA.DATAABERTURA) = MONTH(FE.DATAINICIO) AND YEAR(VA.DATAABERTURA) = YEAR(FE.DATAINICIO)
 ORDER BY DTINICIAL DESC;
 
 select 
        VA.CODAVALIACAO         AS CODAVALIACAO,
        VA.NOME                 AS NOME, 
        VP.CODPARTICIPANTE      AS CODPARTICIPANTE,
        A.CHAPA                   AS CHAPAAVALIADO, 
       (DESCRICAO)                  AS LICENCA,  
       A.DTINICIO                AS DTINICIAL, 
       A.DTFINAL                  AS DTFINAL, 
       VA.DATAABERTURA          AS DTABERTURA,
       'S'                      AS LICENCA,  
       FC.CODPESSOA             AS CODPESSOA
from PFHSTAFT AS A
 INNER JOIN VADPARTICIPANTES AS VP
 ON VP.CHAPAAVALIADO = A.CHAPA
 INNER JOIN VADAVALIACAO AS VA
 ON VA.CODCOLIGADA = VP.CODCOLIGADA
 AND VA.CODAVALIACAO = VP.CODAVALIACAO
 INNER JOIN PFUNC AS FC
 ON A.CHAPA = FC.CHAPA
INNER JOIN PCODAFAST AS D ON A.TIPO = D.CODCLIENTE
WHERE DTINICIO > '2018-01-01' and VA.CODAVALIACAO = 55
 AND MONTH(VA.DATAABERTURA) = MONTH(A.DTINICIO) AND YEAR(VA.DATAABERTURA) = YEAR(A.DTINICIO)
  ORDER BY A.CHAPA DESC;