January 29, 2009 at 5:21 pm
ok, for some reason I have never been asked this in my 5 years working on SQL server (2000).
I need to figure out how to get a list of Month and year for the last 13 months.
so today I would get
jan 2009
dec 2008
nov 2008
.........................
I also would like to get a list with the monday date to the sunday date for the last 13 months
so it would look like
26 jan - 1 feb
19 jan - 25 jan
12 jan - 18 jan
........................
Can anyone help me out? It has to be a SQL 2000 answer, I found a great answer on 2005, but it does not work........:(
I dont want to create table to do it............
January 29, 2009 at 5:50 pm
There are many ways to do this, but an easy way is to create and populate a date table. You can use the function on the link below to load a date table, or function can also be used directly in a query in place of a date table.
Date Table Function F_TABLE_DATE
January 30, 2009 at 7:42 am
if there is a way to it without a table, I would like to know how
January 30, 2009 at 7:57 am
this will give you the months
SELECT
LEFT(CONVERT(VARCHAR,DATEADD(m,-n+1,GETDATE()),0),3) + ' ' + CAST(YEAR(DATEADD(m,-n+1,GETDATE()))AS VARCHAR(4))
FROM Tally
WHERE N < 14
And you can use the same type of query to work out the ranges
If you still having problems let us knwo
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 30, 2009 at 12:02 pm
getting
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Tally'.
January 30, 2009 at 1:25 pm
you have to follow the tutorial in the article above, which shows you how to create a tally table.
Sql server does not store dates in the system.
So there is no magic function that shows you the last 13 months,
The purpose of the tally table is to store the dates, and you query against it to give you the data you need.
February 1, 2009 at 11:23 am
Ray M (1/30/2009)
The purpose of the tally table is to store the dates, and you query against it to give you the data you need.
Just to clarify: the purpose of the Tally table is to provide a sequence of numbers that can then be used to generate a sequence of date values in the range and period that you are interested in.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 1, 2009 at 8:40 pm
ghughes (1/29/2009)
ok, for some reason I have never been asked this in my 5 years working on SQL server (2000).I need to figure out how to get a list of Month and year for the last 13 months.
so today I would get
jan 2009
dec 2008
nov 2008
.........................
I also would like to get a list with the monday date to the sunday date for the last 13 months
so it would look like
26 jan - 1 feb
19 jan - 25 jan
12 jan - 18 jan
........................
Can anyone help me out? It has to be a SQL 2000 answer, I found a great answer on 2005, but it does not work........:(
I dont want to create table to do it............
Heh... it's your funeral for both... formatting dates in SQL Server turns out to be one of the legal forms of career suicide and you need to tell the people that gave you this requirement that formatting dates should be done in the app, not SQL Server. Once dates are converted like this, they cannot be used for sorting, calculating, or ranging unless they are converted back to a date either implicitly or explicitly. Either way, it'll put an extra load on both the server and the app. Same goes if you want local settings to have some effect on the dates. The only exception to the rule is if there is no app.
Also, you not having a Tally table just about condemns you to writing loops and other dreadfully slow code. You need a Tally table. [font="Arial Black"]Please tell us why you don't want to create a table to do this. [/font] If it's yet another requirement from the designers that want you to format the dates, you should show them the following article... might change their mind...
[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/font][/url]
And, if you are either unwilling or unable to convince yourself or the people giving you these requriments of their fallacy, here's some SQL Server 2000 compatible code that will solve both of your problems...
--===== Display the last 13 months as MonYYYY
SELECT RIGHT(CONVERT(CHAR(11),DATEADD(mm,DATEDIFF(mm,0,GETDATE())-Number,0),106),8) AS MonYYYY
FROM Master.dbo.spt_Values
WHERE Number BETWEEN 1 AND 13
AND TYPE = 'P'
--===== Display the last 13 months as week date ranges
SELECT CONVERT(CHAR(6),Week,106)+'-'+CONVERT(CHAR(6),Week+6,106)
FROM (
SELECT DATEADD(wk,DATEDIFF(wk,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))-Number,0) AS Week
FROM Master.dbo.spt_Values
WHERE Number BETWEEN 1 AND 66
AND TYPE = 'P'
)d
WHERE Week >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-13,0)
I know what your next question will be... if I can do that, why do you need a Tally table? Because spt_Values only contains the numbers from 0 to 255 in SQL Server 2000. When you try to do something like the following, you may need 8000 or more numbers...
[font="Arial Black"]Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays[/font][/url]
There's over a dozen basic things you can do across thousands of proplems with a Tally table. Reconsider your statement about not wanting to build a table to do this.
--Jeff Moden
February 1, 2009 at 8:42 pm
ghughes (1/29/2009)
I found a great answer on 2005...
Would you post the URL for that answer, please? Thanks.
--Jeff Moden
February 2, 2009 at 11:25 am
ok, I give up. I defer to everyone greater judgement in the matter. Make perfect sense. I have created table......thank you for the help.
in the 2005 answer, just search the SQL Centeral for 'list of months', that is how I found it
February 2, 2009 at 3:30 pm
[font="Verdana"]To add to the discussion (and to reiterate what was mentioned earlier), I've found the best answer is to keep around a table of dates (I call mine "Calendar", although in our data warehouse it appears as "Dim_Date".)
This allows you to do all sorts of funky thinks like encoding dates in your tables as ints (rather than as datetimes, and constantly having to worry about whether the time part has been filled in), where a simple join to the Calendar table will retrieve the date form.
I store things like day, month, year numbers, the day number within the year (julian-ised date according to Joe Celko: Temporal Data Techniques in SQL[/url]), pre-formatted date names (although yes I agree, better to format dates in the front end if possible), whether the day is a working day or weekend or holiday, even what the financial year/period is for the day.
It's a handy concept. Of course, it's also massive scope creep over what you were asking. But if you keep it around as a permanent table within your database, you will find it useful. I can guarantee that much![/font]
February 2, 2009 at 5:16 pm
Bruce W Cassidy (2/2/2009)
[font="Verdana"]But if you keep it around as a permanent table within your database, you will find it useful. [/font]
I'd suggest ot have it in a separate database, one per server.
Then SQL Server would not need to create a separate cache of this table for calls from different databases, any call from any database will use the same cache.
Less memory used, more users use this table - so less chances that this table will be dropped from cache.
_____________
Code for TallyGenerator
February 2, 2009 at 5:24 pm
Sergiy (2/2/2009)
Bruce W Cassidy (2/2/2009)
[font="Verdana"]But if you keep it around as a permanent table within your database, you will find it useful. [/font]I'd suggest ot have it in a separate database, one per server.
[font="Verdana"]Not a bad suggestion. We do that within our data warehouse as the date dimension.
In an operational environment (er, not DW), I'd suggest local references to it by using synonyms. That way you can move the location of the central store around, and just change the local synonym.
By the way, if you are copying around other "master data", using this approach is a great trick. You have one spot where you update it (one central master data database per instance), and every database on that instance can reference it like a local table by using synonyms.
[/font]
February 2, 2009 at 5:35 pm
ghughes (2/2/2009)
ok, I give up. I defer to everyone greater judgement in the matter. Make perfect sense. I have created table......thank you for the help.in the 2005 answer, just search the SQL Centeral for 'list of months', that is how I found it
That's not so nice... we're just asking you to post a simple URL. Two way street and all, ya know? 😉
--Jeff Moden
February 2, 2009 at 6:41 pm
ok, you are right. At the time I was swamped and trying to fix the next dozen problems on my list. I cant find my link to it, but I did find this and will make it easy to modify and make it work. I spent the last hour looking thru my history so forgive me. 🙁
http://qa.sqlservercentral.com/Forums/Topic494640-149-1.aspx
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply