Consulta Estgiários

SELECT

    Nome AS "NOME",
    Funcional->CargoEfetivo->Descricao AS "CARGO",
    Funcional->DataIngOrgaoFormatada AS "DT. ING.",
        Financeiro->DataDesligamento AS "DESLIG",
        Funcional->TipoServidor->descricao,
        DATEDIFF(YEAR, PESSOA->datanascimento, CAST((:p1 || '-12-31') AS DATE )) AS "IDADE P.REF",
    COUNT('QTD') AS "QTD"

FROM RHCadServidor

WHERE
    ( Funcional->TipoServidor->Codigo IN (10))
    AND ( Funcional->DataIngOrgao <= CAST((:p1 || '-12-31') AS DATE ))
    AND (
            ( Financeiro->DataDesligamento IS NULL )
            OR
            ( Financeiro->DataDesligamento > CAST((:p1 || '-12-31') AS DATE )))
        AND (FUNCIONAL->LOTACAOEXERCICIO->CODIGO NOT IN (200360,200373,200378,200386,200387, 200388, 200359))


ORDER BY
    FUNCIONAL->TIPOSERVIDOR->DESCRICAO,
    Nome