Select from previous months

Question

I need to do select data from previous months, month by month.

My query is like this:

SELECT *
  FROM VIEW_INCD
 WHERE MONTH(DH_CRIA_INCD) = DATEPART(mm,GETDATE()) - 1
   AND YEAR(DH_CRIA_INCD) = YEAR(GETDATE());

It works, I select only from the previous month, and if I need to see the previous one, I put "- 2", "- 3", etc....

The problem is when I need to see December, because the year changes and the query does not return anything. How do I make the year track this subtraction of months?

Answer 1

To get the data for just 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 haven’t tried it; I hope there are no mistakes.

---

If you want to return the entire period in a single query, 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;

Answer 2

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

We use the function DATEADD(MONTH, -4, CONVERT(date, GETDATE())) to subtract 4 months from our current date and use the CONVERT(date, GETDATE()) to ignore the schedule. Observing: If you want to consider the schedule, change the CONVERT(date, GETDATE()) for GETDATE(). If you need to change the amount of months for the last 5, 6, 7... months change the parameter -4 for the amount of days (negatively, as we are subtracting the months) desired -5, -6, -7.

We use the function DATEADD(MONTH, -1, CONVERT(DATE, GETDATE())) 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 just for 1 month as you said in the comments, try running the command below:

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 we are spending the first day of the previous month (because of the -1).

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

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

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

The second part of BETWEEN we are spending the last day of the previous month.

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

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

Answer 3

I would subtract the date in php and pass to the database the desired month and year of query, preferably in a Procedure.

As an alternative I made a query based on yours that will allow you to obtain 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

Answer 4

Very simple using DATEFROMPARTS:

In the WHERE:

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

Parte direita: turning the column date into the first day of the month.

And compare the two :)

Just replace @par with 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)