CODEWITHSUNDEEP
sqlsql-serverwhere-clauserolling-computation
Joshua
Joshua

Reputation: 287

Last 3 Months Where Statement

I've got a SQL statement (SQL Server Management Studio) that I'm passing data into a where-statement into via a dashboard software. The users can select the year (2013 or now 2014) and also the month (which gets passes as a numeric value - so December = 12). I need to adjust the statement to where I get the last 3 months from the year/month they select. Before, b/c the SQL statement was only dealing with 2013 data, it was just the following:

YEAR(Main.ActivityDate) = '@Request.parmYear~'
AND (Month(Main.ActivityDate) Between ('@Request.parmMonth~'-2) and '@Request.parmMonth~')

Normally, parmYear = 2013 and then whatever month they select, it will grab 2 months prior through the current month.

Now, b/c it's January 2014, I need to to grab January 2014 + December 2013 + November 2013. I'm wondering how to adjust the statement to make this happen dynamically.

Thoughts?

Upvotes: 3

Views: 322

Answers (3)

Menelaos
Menelaos

Reputation: 25727

There are two solutions for this.

  • Modify your current where statement and add a condition to check for this case.
  • Use DATEADD function. Present in comments and other answer(s).

Modifying your where to add Condition

Note: Minor error may exists since I need to check if January has month value of zero or 1.

Example:

WHERE 

(
'@Request.parmMonth~'-2 < 1 AND 
YEAR(Main.ActivityDate) = '@Request.parmYear~'-1 AND
Month(Main.ActivityDate) Between 
(12+'@Request.parmMonth~'-2) AND 12  
)
OR
(
YEAR(Main.ActivityDate) = '@Request.parmYear~'
AND (Month(Main.ActivityDate) Between 
('@Request.parmMonth~'-2) and '@Request.parmMonth~'
)

Upvotes: 0

JCalcines
JCalcines

Reputation: 1286

I had a similar problem some time ago. My solution was something like this:

WHERE YEAR(Main.ActivityDate)*12 + YEAR(Month(Main.ActivityDate))
   BETWEEN '@Request.parmYear~'*12+'@Request.parmMonth~'-2 
       AND '@Request.parmYear~'*12+'@Request.parmMonth~'

You could improve this solution sending one parameter as the result of '@Request.parmYear~'*12+'@Request.parmMonth~'.

Upvotes: 1

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103135

I do not have a running SQL Server instance to test this solution but I would suggest constructing a date and using the built in functions to calculate the previous date as those already take into consideration the multiple years etc.

    Declare @requestDate date = DATEFROMPARTS('@Request.parmYear', '@Request.parmMonth', 1);
    ...

    AND Main.ActivityDate between @requestDate AND DATEADD(month, -2, @requestDate )

See this for more details.

Upvotes: 3

Related Questions