Hi P2p members,

I have to write a sql query such that it should generate months and year in (mm/yyyy) format for the last 12 months. for example, if i run that query today... then it should display the results as

11/2005
12/2005
01/2006
02/2006
03/2006
04/2006
.
.
.
09/2006
10/2006

any idea about this query will be of great help...

Thanks in advance

It would be much better to make a permanent "Tally" table of numbers to do these types of things, but in the meantime, this does the trick...

 SELECT RIGHT(CONVERT(VARCHAR(10),DATEADD(mm,-Number,GETDATE()),103),7)
   FROM Master.dbo.spt_Values
  WHERE Name IS NULL
    AND Number BETWEEN 1 AND 12
  ORDER BY -Number

--Jeff Moden

And, just in case you want to make it reusable within a proc... we put the results into a nice little temp table...

     IF OBJECT_ID('TempDB..#PrevMonYear') IS NOT NULL
        DROP TABLE #PrevMonYear

 CREATE TABLE #PrevMonYear
        (
        MonthNum INT PRIMARY KEY CLUSTERED,
        MMYYYY CHAR(7)
        )

 INSERT INTO #PrevMonYear (MonthNum,MMYYYY)
 SELECT MonthNum = -Number,
        MMYYYY = RIGHT(CONVERT(VARCHAR(10),DATEADD(mm,-Number,GETDATE()),103),7)
   FROM Master.dbo.spt_Values
  WHERE Name IS NULL
    AND Number BETWEEN 1 AND 12

 SELECT * FROM #PrevMonYear

--Jeff Moden

Honestly, I'm not trying to pick a fight here, but do you think that using the (mostly undocumented) spt_values table located in the master database is a good idea?

As I understand spt_values, it's a table used internally by various system stored procedures. As such, who knows when some release of SQL Server may change this table? Is it really a good idea to depend on internal system tables containing undocumented values?

It's quick and dirty (and maybe clever) to use it, but is it wise?

I find it handy (and trivial) to create my own sequence number table used for just such purposes as the OP asked about. It may also be useful for such things as finding missing sequence values in a run of values, etc. Many of the databases I've created have such a table defined. Create it once and it's there for any handy purpose.

As an aside, it can also be handy to have a calendar table that just lists dates in sequence. Adding columns to indicate holidays/weekend/business days is also straightforward, and can be VERY useful...

Jeff Mason
Custom Apps, Inc.
[email protected]

Jeff,

I absolutely agree and don't think you're trying to pick a fight... that's why I said as the very first line of my first post...
 


--Jeff Moden

Arul,

Jeff Mason is absolutely correct... you can't rely on the future specific content of Microsoft's system tables... with that in mind, here's one way to make a permanent Tally table (a table with a single column of very well indexed numbers)...

--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
        IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
        ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)

--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC

Then, you can modify the code to create your temp table of months with it's own reference to the number of months back like this...

     IF OBJECT_ID('TempDB..#PrevMonYear') IS NOT NULL
        DROP TABLE #PrevMonYear

CREATE TABLE #PrevMonYear
        (
        MonthNum INT PRIMARY KEY CLUSTERED,
        MMYYYY AS RIGHT(CONVERT(VARCHAR(10),DATEADD(mm,MonthNum,GETD ATE()),103),7)
        )

INSERT INTO #PrevMonYear (MonthNum)
SELECT MonthNum = -N
FROM dbo.Tally
WHERE N BETWEEN 1 AND 12

SELECT * FROM #PrevMonYear

I also added a wicked little change to it... it uses a magical little thing known as a "calculated column". Here's the neat thing about that... if you turn #PrevMonYear into a permanent table, it will ALWAYS show the previous 12 months, as you ask, no matter what the current date (GETDATE()) is... you will NEVER have to recreate it and it will ALWAYS be correct.

The only time you need to be careful with it is if you have a proc that calls the table both before and after midnight of the first day of any month.

--Jeff Moden

Thanks Moden and Mason for the quick response and the possible solutions u guys have provided.

Thanks,
Arul.

Thank you for the feedback. :D

--Jeff Moden

Just a tip.
Change "Name IS NULL" to "Type = 'p'" and you will get an index seek on spt_values!