?
Solved

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

Posted on 2013-01-17
3
Medium Priority
?
397 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 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

850 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