Error executing a subquery in sql server

Question

I am trying to return values where the date is between two fields of my page this field and max(field)

This is the consultation:

SELECT      COD_SECAO
        ,   SECAO
        ,   CHAPA
        ,   NOME
        ,   DTNASCIMENTO
        ,   DATAADMISSAO
        ,   INICIO_PERIODO
        ,   FIM_PERIODO
        ,   INICPROGFERIAS1
        ,   FIMPROGFERIAS1
        ,   (INICIO_PERIODO + 365) AS LIMITE1
        ,   (INICIO_PERIODO + 670) AS LIMITE2  
FROM    ( 
            SELECT  PSECAO.CODIGO       AS COD_SECAO
                ,   PSECAO.DESCRICAO    AS SECAO 
                ,   PFUNC.CHAPA         AS CHAPA 
                ,   PFUNC.NOME          AS NOME 
                ,   PFUNC.CODSITUACAO   AS SITUACAO 
                ,   (
                        SELECT  MAX(PFHSTFER.DTINIPERAQUIS) 
                        FROM    PFHSTFER 
                        WHERE   PFHSTFER.CODCOLIGADA    = PFUNC.CODCOLIGADA 
                            AND PFHSTFER.CHAPA          = PFUNC.CHAPA 
                            AND PFUNC.CODCOLIGADA       = 1 
                            AND PFHSTFER.DTFIMGOZO      IS NULL 
                    )                   AS INICIO_PERIODO 
                ,   ( 
                        SELECT  MAX(PFHSTFER.DTFIMPERAQUIS) 
                        FROM    PFHSTFER 
                        WHERE   PFHSTFER.CODCOLIGADA    = PFUNC.CODCOLIGADA 
                            AND PFHSTFER.CHAPA          = PFUNC.CHAPA 
                            AND PFUNC.CODCOLIGADA       = 1 
                            AND PFHSTFER.DTFIMGOZO      IS NULL 
                        HAVING  (MAX(PFHSTFER.DTFIMPERAQUIS) BETWEEN '2019-03-01' AND '2019-03-30')
                    )                   AS FIM_PERIODO 
                ,   PFUNC.INICPROGFERIAS1 
                ,   PFUNC.FIMPROGFERIAS1 
                ,   PFUNC.DATAADMISSAO 
                ,   PPESSOA.DTNASCIMENTO 
            FROM    PFUNC 
                ,   PSECAO 
                ,   PPESSOA  
            WHERE   PFUNC.CODCOLIGADA               = PSECAO.CODCOLIGADA  
                AND PFUNC.CODSECAO                  = PSECAO.CODIGO 
                AND PFUNC.CODPESSOA                 = PPESSOA.CODIGO 
                AND PFUNC.CODCOLIGADA               = 1 
                AND PFUNC.CODSITUACAO               NOT IN ('D', 'I', 'T', 'P', 'U', 'L', 'R') 
                AND PFUNC.CODTIPO                   IN ('N', 'T') 
                AND SUBSTRING(PFUNC.CODSECAO, 7, 2) != '99' 
                AND SUBSTRING(PFUNC.CODSECAO, 3, 3) IN ('004')   
                AND PFUNC.CODSECAO                  = '1.004.07.01.01574.001'   
        ) X 
ORDER BY    LIMITE2 ASC
        ,   COD_SECAO
        ,   NOME

The problem is that fim_periodo is returning null, when in fact while taking the between it returns the maximum values of the fields.

Answer 1

I think the best way to do that is by not using the having. Since it’s only bringing one field, you can leave the filter on where even:

SELECT MAX(PFHSTFER.DTFIMPERAQUIS) 
  FROM PFHSTFER 
 WHERE PFHSTFER.CODCOLIGADA = PFUNC.CODCOLIGADA 
   AND PFHSTFER.CHAPA = PFUNC.CHAPA 
   AND PFUNC.CODCOLIGADA = 1 
   AND PFHSTFER.DTFIMGOZO IS NULL 
   AND PFHSTFER.DTFIMPERAQUIS BETWEEN '2019-03-01' AND '2019-03-30'

Detail: using functions in filters can end the query performance as explained at that link. In that case, you’re using the between in subconsultation for each return line, searching in the table PFHSTFER - If it is too big, the consultation can be slow.