CODEWITHSUNDEEP
sqlsql-serverreporting
Legend1989
Legend1989

Reputation: 664

SQL Last Three Months

I have wrote SQL to select user data for last three months, but I think at the moment it updates daily.

I want to change it so that as it is now October it will not count Octobers data but instead July's to September data and change to August to October when we move in to November

This is the SQL I got at the moment:

declare @Today datetime
declare @Category varchar(40)
set @Today = dbo.udf_DateOnly(GETDATE())

set @Category = 'Doctors active last three months updated'          
declare @last3monthsnew datetime 
set @last3monthsnew=dateadd(m,-3,dbo.udf_DateOnly(GETDATE()))          
delete from LiveStatus_tbl where Category = @Category          


  select @Category, count(distinct U.userid)         
  from   UserValidUKDoctor_vw U        
  WHERE  LastLoggedIn >= @last3monthsnew

How would I edit this to do that?

Upvotes: 0

Views: 349

Answers (3)

Recursive
Recursive

Reputation: 952

How about using BETWEEN ?

WHERE LastLoggedIn 
BETWEEN
DATEADD(month, DATEDIFF(month, 0, GETDATE())-3, 0) 
AND 
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

Upvotes: 0

Tanner
Tanner

Reputation: 22743

Referencing this answer to get the first day of the month:

How can I select the first day of a month in SQL?

You can detect the month limitations like so:

select DATEADD(month, DATEDIFF(month, 0, getdate()) - 3, 0) AS StartOfMonth
select DATEADD(month, DATEDIFF(month, 0, getdate()), 0) AS EndMonth

Then you can add that into variables or directly into your WHERE clause:

declare @StartDate datetime
declare @EndDate datetime

set @StartDate = DATEADD(month, DATEDIFF(month, 0, getdate()) - 3, 0) 
set @EndDate = DATEADD(month, DATEDIFF(month, 0, getdate()), 0) 

select @Category, count(distinct U.userid)         
from   UserValidUKDoctor_vw U        
where LastLoggedIn >= @StartDate AND LastLoggedIn < @EndDate

Or:

select @Category, count(distinct U.userid)         
from   UserValidUKDoctor_vw U        
where LastLoggedIn >= DATEADD(month, DATEDIFF(month, 0, getdate()) - 3, 0) 
  and LastLoggedIn < DATEADD(month, DATEDIFF(month, 0, getdate()), 0)

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

WHERE  LastLoggedIn >= DATEADD(month, DATEDIFF(month, 0, GETDATE())-3, 0)
 AND   LastLoggedIn <  DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

The above statement will return any results in July till before the start of current month.

Upvotes: 2

Related Questions