• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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.  
0
Jintonix415
Asked:
Jintonix415
  • 4
  • 4
  • 2
  • +1
2 Solutions
 
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
 
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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
 
djon2003Commented:
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
 
ThomasianCommented:
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
 
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now