"order" issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

"order" issue

Author

Topic

Oliver wang
Yak Posting Veteran

50 Posts

Posted - 2011-03-11 : 05:36:14

Hello everyone,

I have a query which can return all the months in a time period. The scripts is like following:

select cast(right(convert(varchar(7),dateadd(month,number,'2009-10-01'),120),2) as int)
from master..spt_values
where type='P' and dateadd(month,number,'2009-10-01')<='2011-03-01'

and the result is as below:
10
11
12
1
2
3

My problem is I need the exact above order(not 1,2,3,10,11,12). If i left join some other tables, the previous order will be shuffled. I have considered using a row_number() function, but it also needs to first choose an "order by" column. Does anybody have some insight on this issue? Thank you very much for your attention.

regards,

Oliver

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-03-11 : 06:11:35

You need to include the year:


SELECT YEAR(DATEADD(month, number, '2009-10-01')) AS [Year]
	,MONTH(DATEADD(month, number, '2009-10-01')) AS [Month]
FROM [master].dbo.spt_values
WHERE [type]='P'
	AND DATEADD(month, number, '20091001') <= '20110301'
ORDER BY [Year], [Month]

-- or

SELECT YEAR(DATEADD(month, number, '2009-10-01')) * 100
	+ MONTH(DATEADD(month, number, '2009-10-01')) AS [Month]
FROM [master].dbo.spt_values
WHERE [type]='P'
	AND DATEADD(month, number, '20091001') <= '20110301'
ORDER BY [Month]

Oliver wang
Yak Posting Veteran

50 Posts

Posted - 2011-03-12 : 00:55:22

Thank you a million~Ifor~ your solution works perfectly

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-12 : 23:04:48

>> I have a query which can return all the months in a time period. <<

In SQL, we have temporal data types. You are writing 1950's COBOL where dates were strings but you are using SQL. This is awful programming. And you did this awful code in dialect! SQL Programers CAST, not CONVERT. The term Master is an old file system convention. There is no “magic_vague_type”; in logic and therefore in RDBMS, to be is to be something in particular. Think how vague “number” is.

>> My problem is I need the exact above order(not 1,2,3,10,11,12). <<

There is no concept of ordering in RDBMS; we use sets and not sequences. It is also a database language, not a computational language.

Thanks to your sequential mindset (I.e you are stuck in COBOL and mag tapes) you cannot see that a month exists in an EXPLICIT year. MySQL has a convention of using zeros for the day and month fields in an ISO-8601 format. Try this:

CREATE TABLE Month_Calendar
(month_name CAR(10) NOT NULL PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CHECK(start_date < end_date));

INSERT INTO Month_Calendar
VALUES ('2011—01-00', '2011-01-01', '2011-02-31'),-- 2011 January
('2012—01-00', '2012-01-01', '2012-02-31'),-- 2012 January

Now do joins and not string manipulation.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources