Select from previous months

Pretty simple using DATEFROMPARTS:

No WHERE :

Parte esquerda : we are transforming the current date on the first day of the month and unloading the number "@par" times.

Parte direita : transforming the column date on the first day of the month.

And we compared the 2:)

Just replace @par by the number of months: -1, -2 ...

SELECT * FROM VIEW_INCD WHERE dateadd(month, @par, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)) = DATEFROMPARTS(YEAR(DH_CRIA_INCD), MONTH(DH_CRIA_INCD), 1)

To get data for only one month, try

-- código #1
-- informe número de meses a retroagir
declare @RetroMes tinyint;
set @RetroMes= 1;

-- calcula período mensal de consulta
declare @DataInicial date, @DataFinal date;
set @DataInicial= dateadd(month, datediff(month, 0, current_timestamp) - @RetroMes, 0);
set @DataFinal= dateadd(month, +1, @DataInicial); 

--
SELECT colunas
  from VIEW_INCD
  where DH_CRIA_INCD >= @DataInicial 
        and DH_CRIA_INCD < @DataFinal;

I did not test; I hope there are no mistakes.

If you want a single query to return the whole period, try the following code:

-- código #2
-- informe número de meses do período
declare @RetroMes tinyint;
set @RetroMes= 4;

-- calcula período mensal de consulta
declare @DataInicial date, @DataFinal date;
set @DataFinal= dateadd(month, datediff(month, 0, current_timestamp), 0);
set @DataInicial= dateadd(month, -@RetroMes, @DataFinal);

--
SELECT year(DH_CRIA_INCD) as Ano, 
       month(DH_CRIA_INCD) as Mês,
       colunas
  from VIEW_INCD
  where DH_CRIA_INCD >= @DataInicial 
        and DH_CRIA_INCD < @DataFinal;

We need to filter the last 4 months (according to your comments) from the current system month.

We used the DATEADD (MONTH, -4, CONVERT (date, GETDATE ()) function to subtract 4 months from our current date and use CONVERT (date, GETDATE () ) to ignore the time. Note: If you want to consider the time, change the CONVERT (date, GETDATE ()) to GETDATE () . If you need to change the number of months for the last 5, 6, 7 ... months, change the parameter -4 to the number of days (negatively, since we are subtracting the months) > -5, -6, -7 .

We used the DATEADD (MONTH, -1, CONVERT (DATE, GETDATE ()) function to ignore the current month.

SELECT *
  FROM VIEW_INCD
 WHERE DH_CRIA_INCD BETWEEN DATEADD(MONTH, -4, CONVERT(date, GETDATE())) AND DATEADD(MONTH, -1, CONVERT(DATE, GETDATE()))
 ORDER BY DH_CRIA_INCD DESC

To run a query for just 1 month, you said in the comments, try the following command:

SELECT *
  FROM VIEW_INCD
 WHERE DH_CRIA_INCD BETWEEN DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 1, 0)
   AND DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))
 ORDER BY DH_CRIA_INCD DESC

The first part of BETWEEN is passing the first day of the previous month (because of -1).

Note: If you want to change the month add -1, -2, -3 after the DATEADD function, but -1 refers to 1 previous month, -2 refers to 2 months previous, and so on.

Example: Since GETDATE () will return on 08/08/2018, the function below will return 01/07/2018.

 DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 1, 0)

The second part of BETWEEN is passing the last day of the previous month.

Note: If you want to change the month add -1, -2, -3 after the DATEADD function, but -1 refers to 2 previous months, -2 refers to 3 months previous, and so on. Example: Whereas GETDATE () will return on 08/08/2018, the function below will return 06/30/2018.

DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 1, 0))

I would do the subtraction of the date in php and would pass to the bank the month and year of the desired query, preferably in a Procedure.

As an alternative I made a query based on yours that will allow you to get the desired result, it is worth mentioning that this solution is limited to four months that is your need.

SQLFiddle - Example:

SELECT * 
FROM VIEW_INCD
WHERE 
  MONTH(DH_CRIA_INCD) = 
  CASE DATEPART(mm,GETDATE()) -8
    WHEN 0  THEN 12
    WHEN -1 THEN 11
    WHEN -2 THEN 10
    WHEN -3 THEN 9
  ELSE
    DATEPART(mm,GETDATE()) - 8
  END
AND 
  YEAR(DH_CRIA_INCD) =
  CASE WHEN DATEPART(mm,GETDATE()) - 8 < 1 THEN
    YEAR(GETDATE()) - 1
  ELSE
    YEAR(GETDATE())
  END