0

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?

Arion
  • 31,011
  • 10
  • 70
  • 88
Legend1989
  • 664
  • 3
  • 9
  • 23

3 Answers3

2
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.

M.Ali
  • 67,945
  • 13
  • 101
  • 127
0

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)
Community
  • 1
  • 1
Tanner
  • 22,205
  • 9
  • 65
  • 83
0

How about using BETWEEN ?

WHERE LastLoggedIn 
BETWEEN
DATEADD(month, DATEDIFF(month, 0, GETDATE())-3, 0) 
AND 
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
Recursive
  • 954
  • 7
  • 12