Getting Last 12 Month-Year from present date

Question

Getting Last 12 Month-Year from present date

gaurav-404321

SSCommitted

Points: 1593
More actions
November 24, 2010 at 6:32 am

#229053

I need a query that should return me list of last 12 months from the date and time of running the query.
For example
If i am runnning the query today it should give me list like:
Nov-2010
Oct-2010
Sep-2010
Aug-2010
Jul-2010
Jun-2010
May-2010
Apr-2010
Mar-2010
Feb-2010
Jan-2010
Dec-2009
Nov-2009
Please suggest a query

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

How many months do you want, is it 12 or 13?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gaurav-404321 SSCommitted Points: 1593 More actions · Answer 2

gaurav-404321

SSCommitted

Points: 1593

November 24, 2010 at 6:44 am

#1255041

Please suggest for n no of months.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

gaurav-404321 (11/24/2010)
Please suggest for n no of months.

No problem:

DECLARE @Today DATETIME, @nMonths TINYINT

SET @Today = GETDATE()

SET @nMonths = 12

SELECT MonthEndDate = DATEADD(dd, -1, DATEADD(month, n.n + DATEDIFF(month, 0, @Today),0))

FROM (SELECT TOP(@nMonths) n = ROW_NUMBER() OVER (ORDER BY NAME) FROM master.dbo.syscolumns) n

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gaurav-404321 SSCommitted Points: 1593 More actions · Answer 4

gaurav-404321

SSCommitted

Points: 1593

November 24, 2010 at 6:57 am

#1255053

Thanks for quick reply.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

You're welcome 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Cory E. SSCertifiable Points: 7076 More actions · Answer 6

I am only extending the result set to match what the op requested...

DECLARE @Today DATETIME

, @nMonths TINYINT

SET @Today = GETDATE()

SET @nMonths = 12

SELECT MonthEndDate = DATEADD(dd, -1, DATEADD(month, n.n + DATEDIFF(month, 0, @Today), 0))

, SUBSTRING(

DATENAME(MONTH, DATEADD(dd, -1, DATEADD(month, n.n + DATEDIFF(month, 0, @Today), 0))), 1, 3)

+ '-'

+ CAST(YEAR(DATEADD(dd, -1, DATEADD(month, n.n + DATEDIFF(month, 0, @Today), 0))) AS VARCHAR(4))

AS EndDateOP

FROM ( SELECT TOP ( @nMonths )n = ROW_NUMBER() OVER ( ORDER BY NAME )

FROM master.dbo.syscolumns ) n

ORDER BY 1 DESC

-- Cory

gaurav-404321 SSCommitted Points: 1593 More actions · Answer 7

Hi, I have tried running this query,

but this is giving me dates starting from 1year plus from today's date till and back to previous 12months. AS it is starting from 2011 year and giving result till Nov,2010.

But it should start from today's year (2010) and move back to previous 12 months like Nov,2009.

It is giving:

Dec-2011

Nov-2011

Oct-2011

Sep-2011

Aug-2011

Jul-2011

Jun-2011

May-2011

Apr-2011

Mar-2011

Feb-2011

Jan-2011

Dec-2010

Nov-2010

It should give result as:

Dec-2010

Nov-2010

Oct-2010

Sep-2010

Aug-2010

Jul-2010

Jun-2010

May-2010

Apr-2010

Mar-2010

Feb-2010

Jan-2010

Dec-2009

Nov-2009

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Which query are you running? If you altered the code, best post it here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gaurav-404321 SSCommitted Points: 1593 More actions · Answer 9

I have not altered the code. Its same as suggested by you yesterday:

DECLARE @Today DATETIME, @nMonths TINYINT

SET @Today = GETDATE()

SET @nMonths = 12

SELECT MonthEndDate = DATEADD(dd, -1, DATEADD(month, n.n + DATEDIFF(month, 0, @Today),0))

FROM (SELECT TOP(@nMonths) n = ROW_NUMBER() OVER (ORDER BY NAME) FROM master.dbo.syscolumns) n

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Sorry my bad - this matches your original requirement in your first post.

You would benefit from experimenting with the code and understanding how it works:)

DECLARE @Today DATETIME, @nMonths TINYINT

SET @Today = GETDATE()

SET @nMonths = 13

SELECT MonthEndDate = DATEADD(dd, -1, DATEADD(month, (2-n.n) + DATEDIFF(month, 0, @Today),0))

FROM (SELECT TOP(@nMonths) n = ROW_NUMBER() OVER (ORDER BY NAME) FROM master.dbo.syscolumns) n

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

vbernys Valued Member Points: 69 More actions · Answer 11

This was exactly the solution I was looking for! I appreciate the post, it is nice to be able to search for a solution that works. Thank you:-D