Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 6th, 2006, 07:20 AM
Registered User
 
Join Date: Nov 2006
Location: Chennai, Tamil Nadu, India.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Sql Query to generate months and year

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

Reply With Quote
  #2 (permalink)  
Old November 6th, 2006, 08:36 AM
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old November 6th, 2006, 09:03 AM
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old November 6th, 2006, 09:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
je.mason@comcast.net
Reply With Quote
  #5 (permalink)  
Old November 6th, 2006, 10:55 PM
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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...
 
Quote:
quote: It would be much better to make a permanent "Tally" table of numbers to do these types of things,



--Jeff Moden
Reply With Quote
  #6 (permalink)  
Old November 7th, 2006, 12:05 AM
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old November 7th, 2006, 05:49 AM
Registered User
 
Join Date: Nov 2006
Location: Chennai, Tamil Nadu, India.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Thanks,
Arul.

Reply With Quote
  #8 (permalink)  
Old November 7th, 2006, 08:32 PM
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Thank you for the feedback. :D

--Jeff Moden
Reply With Quote
  #9 (permalink)  
Old July 23rd, 2007, 04:42 PM
Friend of Wrox
 
Join Date: May 2006
Location: Helsingborg, , Sweden.
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
sql query to generate counts by month lethe SQL Server 2000 9 March 7th, 2017 12:08 AM
T-SQL Code to get last 6 months midway11 SQL Language 1 October 15th, 2006 08:59 AM
Query Help - Display numbers from two Months rkellogg Access 2 September 27th, 2006 08:51 AM
Generate SQL Script shaileshk SQL Server 2000 3 September 6th, 2005 03:12 PM
How to generate SQL querries mistry_bhavin General .NET 1 August 12th, 2004 11:42 AM



All times are GMT -4. The time now is 11:35 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.