SQL query to get all records from last month but until the current day

Question

Good morning, I need to know a percent variation of last month’s job records for the task logs you did this month. For this I need to compare with the current day, for example.

If today were 15/10/2015 I need to count how many tasks have been opened between 01/09/2015 until 15/09/2015 remembering that this needs to be done automatically, that is, the 15th mentioned in the example, has to be picked the date of the machine. I just got into it:

select *
from Solicitacao 
where UsuIDGrupoRespConclusao = 2655 and 
DATEPART(m, SolDataFechamento) = DATEPART(m, DATEADD(m, -1, getdate()))
AND DATEPART(yyyy, SolDataFechamento) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

This query returns the open tasks last month INTEGER, as I need the variation, I have to compare from the beginning until the current day of last month.

OBS: I am using SQL SERVER 2008

Answer 1

Dude, only last month is exactly what you did, but you don’t need to subtract a year from the current day. What you’re asking for query is: I want all requests that had the closing date(Welding) last month

( AND DATEPART(m, Welding Machining) = DATEPART(m, DATEADD(m, -1,GETDATE())) )

and last year

DATEPART(yyyy, Welding Machining) = DATEPART(yyyy, DATEADD(m, -1, GETDATE()))

Just take it out of the sentence:

SELECT *
FROM Solicitacao  
WHERE
UsuIDGrupoRespConclusao = 2655
AND DATEPART(m, SolDataFechamento) = DATEPART(m, DATEADD(m, -1, GETDATE()))
AND DATEPART(YEAR, SolDataFechamento) = DATEPART(YEAR, GETDATE())

But if you want all the data from last month until today exactly, that is, data from 01/09 until 30/09 (if today), just add one more condition for it, that is:

AND DATEPART(DAY, Welding) <= DATEPART(DAY, GETDATE())

I hope I’ve helped!

Answer 2

Hello, see the example below how you can be doing

    declare @data datetime = (select getdate())

    declare @ano int , @mes int
    set @mes = (select month(@data)) 
    set @ano = (select year(@data))


    DECLARE @dataInicio DATE
    DECLARE @dataFinal DATE

    DECLARE @date1 DATETIME =  REPLACE(@ano, CHAR(0), '') + '-01-' +REPLACE(@mes - 1, CHAR(0), '') -- @mes - 1 mes anterior ao atual
    DECLARE @date2 DATETIME =   REPLACE(@ano, CHAR(0), '') + '-01-' +REPLACE(@mes, CHAR(0), '') 
    set @dataInicio =  (SELECT DATEADD(mm, DATEDIFF(mm, 0, @date1), 0))
    set @dataFinal = (SELECT DATEADD(ms ,-3 ,DATEADD(mm, DATEDIFF(mm, 0, @date2), 0)))

    select @dataInicio, @dataFinal

---
and SolDataFechamento between @dataInicio and @dataFinal

Answer 3

Take a look if that’s what you need:

WHERE NOME_DO_SEU_TABLE > GETDATE() -30

With the GETDATE() command you can take the current date and put a date range in front of it, just like I did above, selecting all the values you have in your table with -30 days of the current date.

Answer 4

declare @hoje datetime
set @hoje = '20151015'

declare @dataend datetime
set @dataend = DATEADD(MONTH, -1, @hoje)


declare @datainit datetime
set @datainit = DATEADD(DAY, -(DAY(@dataend) - 1), @dataend)

select @datainit, @dataend