Declaring a variable in a one month period one year ago

I would like to declare a variable for purchases in a one month period starting a year ago. So if the current month is Feb 2011, then I would want to see purchases made in Feb 2010.  
Jintonix415Asked:
Who is Participating?
 
ThomasianConnect With a Mentor Commented:
Declare @12M as date 
SET @12M = DATEADD(month,DATEDIFF(month,0,GETDATE())-12,0)

SELECT *
FROM Purchases
WHERE
	PurchaseDate >= @12M
	AND PurchaseDate < DATEADD(month,1,@12M)

Open in new window

0
 
djon2003Commented:
Using DATEADD and DATEPART functions you can figure out a month range any when using today's date.


So as you asked, let say the field name is called MyDate.

DATEADD('dd', DATEPART('dd', MyDate) * -1 + 1, DATEADD('yyyy', -1, MyDate))

So this would be the first day of the Feb 2010 month.

DATEADD('mm', 1, DATEADD('dd', DATEPART('dd', MyDate) * -1 + 1, DATEADD('yyyy', -1, MyDate)))

This would give you the next month, March 2010.

So using :
TestedDate >= DATEADD('dd', DATEPART('dd', MyDate) * -1 + 1, DATEADD('yyyy', -1, MyDate)) AND TestedDate < DATEADD('mm', 1, DATEADD('dd', DATEPART('dd', MyDate) * -1 + 1, DATEADD('yyyy', -1, MyDate)))

Would give you the data rows which the column TestedDate is in the month one year ago MyDate.
0
 
Jintonix415Author Commented:
Thanks Djon, but how would you declare that as a Variable? Lets say I declare a variable @12M. If I use that variable in one of my select queries it would basically find whatever I am looking for when I use the variable 12M in my where clause.  
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
djon2003Commented:
Create a function in the database which takes either one or two dates. (One if the date to calculate from is today, otherwise two).

So, this function would return a boolean which is the condition I gave above. From then, you'll be able to use this function into any query.
0
 
Jintonix415Author Commented:
Not quite sure I understand as my task is to create a variable that performs these functions instead of using the functions as is in the query.
0
 
djon2003Commented:
How would you like to use this ?

Let make a precision. At first, a range is two variables for the two borders. Anyhow, answer me first and I'll figure out after.
0
 
Jintonix415Author Commented:
Basically I like to Declare a variable. For example

Declare @12M as date
set @12M =

the @12M variable will equal the month one year from the current month. So if I want to find purchases made using that variable it will return purchases made in Oct 2010 since it is currently October. Hope this makes sense.  

0
 
djon2003Connect With a Mentor Commented:
So you will have to set two variables. Because the date functions works only in a query, let's assign the variable as below.
Declare @12M-Min as date 
Declare @12M-Max as date 
SELECT @12M-Min = DATEADD('dd', DATEPART('dd', GETDATE()) * -1 + 1, DATEADD('yyyy', -1, GETDATE())), @12M-Max = DATEADD('mm', 1, DATEADD('dd', DATEPART('dd', GETDATE()) * -1 + 1, DATEADD('yyyy', -1, GETDATE())))

Open in new window


As I understand from what you say, the past month as to be located from today's month. So I changed my MyDate var by GETDATE() function which returns today.

Now you can use these variables like that :
SELECT * FROM MyTable WHERE TestedDate >= @12M-Min AND TestedDate < @12M-Max

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you might want to read this article to know some basic stuff about date/time handling in databases:
http://www.experts-exchange.com/A_1499.html

from there , and above statement, you should be able to solve the issue.
0
 
Jintonix415Author Commented:
Thanks Angel, that would be very useful.

I think I prefer to use Thomasians set variables as that seems most simplistic, although your logic can also perform the job.

Thomasian, using your variable how would I be able to display those who would have at least one purchase in either of the first two months but not in the third if I declare two more variables ie:

Declare @12M as date
SET @12M = DATEADD(month,DATEDIFF(month,0,GETDATE())-12,0)
Declare @11M as date
SET @11M = DATEADD(month,DATEDIFF(month,0,GETDATE())-11,0)
Declare @10M as date
SET @10M = DATEADD(month,DATEDIFF(month,0,GETDATE())-10,0)

I can also create a separate question for this, thanks!
0
 
ThomasianCommented:
Try if this works      *Not tested
Declare @12M as date 
SET @12M = DATEADD(month,DATEDIFF(month,0,GETDATE())-12,0)

SELECT *
FROM Purchases
WHERE	(SELECT
		COUNT(DISTINCT CASE WHEN DATEDIFF(d,@12M,PurchaseDate)<2 THEN 1 END)
		+ COUNT(DISTINCT CASE WHEN DATEDIFF(d,@12M,PurchaseDate)=2 THEN 1 END)*3
	WHERE
		PurchaseDate >= @12M
		AND PurchaseDate < DATEADD(month,3,@12M)
	)=2

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.