Solved

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

Posted on 2013-01-17
3
381 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
3 Comments
 
LVL 22

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 43

Accepted Solution

by:
zephyr_hex (Megan) earned 500 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql help 8 58
SQL Server 2012 r2 - Varible Table 3 31
MS SQL Update query with connected table data 3 38
Index and Stats Management-Specific tables 8 22
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 Copy Database Wizard 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.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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