DATE CRITERIA ENTER A SUBQUERY - SQL

Asked

Viewed 57 times

-3

I am doing a query that the boundary dates are based on subquerys, I would like to put a condition so that the boundary column is between data1 and data2 follows the query:

    SELECT PSECAO.CODIGO COD_SECAO,
          PSECAO.DESCRICAO SECAO,
          PFUNC.CHAPA CHAPA,
          PFUNC.NOME NOME,
          PFUNC.CODSITUACAO 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 ) 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 ) FIM_PERIODO,

    (SELECT MAX(PFHSTFER.DTINIPERAQUIS) + 365
      FROM PFHSTFER
      WHERE PFHSTFER.CODCOLIGADA = PFUNC.CODCOLIGADA
        AND PFHSTFER.CHAPA = PFUNC.CHAPA
        AND PFUNC.CODCOLIGADA = 1
        AND PFHSTFER.DTFIMGOZO IS NULL ) LIMITE1 ,

         (SELECT MAX(PFHSTFER.DTINIPERAQUIS) + 670
      FROM PFHSTFER
      WHERE PFHSTFER.CODCOLIGADA = PFUNC.CODCOLIGADA
        AND PFHSTFER.CHAPA = PFUNC.CHAPA
        AND PFUNC.CODCOLIGADA = 1
        AND PFHSTFER.DTFIMGOZO IS NULL ) LIMITE2,
          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' 
ORDER BY COD_SECAO,
         NOME

I’d like to have something like having limite2 between '2019-05-01' and '2019-05-30'.

but he won’t accept.

sql sql-server tsql

1 answer

0

Try this:

    SELECT *
    FROM (
    SELECT PSECAO.CODIGO COD_SECAO,
      PSECAO.DESCRICAO SECAO,
      PFUNC.CHAPA CHAPA,
      PFUNC.NOME NOME,
      PFUNC.CODSITUACAO 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 ) 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 ) FIM_PERIODO,

(SELECT MAX(PFHSTFER.DTINIPERAQUIS) + 365
  FROM PFHSTFER
  WHERE PFHSTFER.CODCOLIGADA = PFUNC.CODCOLIGADA
    AND PFHSTFER.CHAPA = PFUNC.CHAPA
    AND PFUNC.CODCOLIGADA = 1
    AND PFHSTFER.DTFIMGOZO IS NULL ) LIMITE1 ,

     (SELECT MAX(PFHSTFER.DTINIPERAQUIS) + 670
  FROM PFHSTFER
  WHERE PFHSTFER.CODCOLIGADA = PFUNC.CODCOLIGADA
    AND PFHSTFER.CHAPA = PFUNC.CHAPA
    AND PFUNC.CODCOLIGADA = 1
    AND PFHSTFER.DTFIMGOZO IS NULL ) LIMITE2,
      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' 
 ) AS X

 WHERE X.LIMITE2 BETWEEN '20190501' AND '20190530'

 ORDER BY COD_SECAO,
     NOME

Browser other questions tagged sql sql-server tsql

You are not signed in. Login or sign up in order to post.