?
Solved

Get past 2 months full data and current month data up to now

Posted on 2013-01-17
3
Medium Priority
?
393 Views
Last Modified: 2013-01-17
I need to run a query that gets the prior 2 months of data and then then current month's data up to the current date. When the current month ends, I need it to tic forward and continue the cycle. So, on January 17th, I would want all of November, all of December and January until now (the 17th). On February 1st, I would want all of December, all of January and February up to the 1st, and so on.
0
Comment
Question by:LCNW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 23

Expert Comment

by:Steve Wales
ID: 38789234
Here's some code to get you First of this month, last month, the month before that and Today:

DECLARE @Today       DATETIME,
        @FirstThis   DATETIME,
        @FirstLast   DATETIME,
        @FirstTwo    DATETIME


SELECT @Today = dateadd(dd, datediff(dd, 0, getdate())+0, 0)     -- Today's Date, midnight past
SELECT @FirstThis = DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today))  -- First of this month
SELECT @FirstLast = DATEADD(mm, -1, @FirstThis)
SELECT @FirstTwo  = DATEADD(mm, -2, @FirstThis)

Print @FirstThis
Print @FirstLast
Print @FirstTwo
Print @Today

Output:

Jan  1 2013 12:00AM
Dec  1 2012 12:00AM
Nov  1 2012 12:00AM
Jan 17 2013 12:00AM

Open in new window


You could then run your query where dates are between @FirstTwo & @Today

Be careful if you've got times in your date fields since my @Today is midnight at the start of today in this example.

If you wanted Midnight of the first two months back up to current, just set today to @getdate()
0
 
LVL 44

Accepted Solution

by:
zephyr_hex (Megan) earned 2000 total points
ID: 38789241
i assume you have a datefield you'll be comparing to.

where
DateField>= dateadd(month, datediff(month, 0, getdate()) - 2, 0)  //this is Oct 1, 2012
and DateField <= getdate()   //this is today, Jan 17, 2013
0
 
LVL 1

Author Closing Comment

by:LCNW
ID: 38789271
Spot on, thank you.
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question