Solved

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

Posted on 2013-01-17
3
386 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 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

690 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