SQL query to retrieve all records from last month but up to the current day

Navigation
6

Good morning, I need to know a% change from last month's job records to job records made this month. For this I need to compare with the current day, for example.

If today was 10/15/2015 I need to count how many tasks were opened between 01/09/2015 until 9/15/2015 remembering that this needs to be done automatically, that is, the 15th day quoted in the example has to be taken date of the machine. I just came up with this: 

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 tasks opened last month INTEGER, as I need the variation, I have to compare from the beginning to the current day of last month.

OBS: I'm using SQL SERVER 2008

    
asked by anonymous 30.10.2015 / 12:28

4 answers

2

Dude, only last month is exactly what you did, but you do not have to subtract one year from the current day. What you are requesting for a query is: I want all the requests that had the closing date (SolDataFechamento) last month

(AND DATEPART (m, SolDataFace) = DATEPART (m, DATEADD (m, -1, GETDATE ())))

and last year

DATEPART (yyyy, SolDataFace) = DATEPART (yyyy, DATEADD (m, -1, GETDATE ())

Just remove this from 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 to today exactly, that is, data from 01/09 to 30/09 (case today), just add one more condition to it, which is:

  

AND DATEPART (DAY, SolDataClose)

30.10.2015 / 13:06
1

Hello, see the example below how you can do this 

    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
    
30.10.2015 / 12:59
0
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
    
30.10.2015 / 13:18
0

Take a look if that's what you need: 

WHERE NOME_DO_SEU_TABLE > GETDATE() -30

With the GETDATE () command you can get the current date and put a range of dates ahead of it, just like I did above, selecting all the values that are in your table with -30 days from the current date. / p>     

30.10.2015 / 12:52
Retrieve dynamic input and insert into bank with procedure How do I set a token in the header?