Order/Sort By - 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 2012 Forums

Transact-SQL (2012)

Order/Sort By

Author

Topic

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-07 : 11:49:34

Evening All

I wonder if you can help me, I have created a table but when I query the data I don't get the results in the right order:

Period 1
Period 10
Period 11
Period 12
Period 2

What I would like to see is (just adding a 0 to the first 9 numbers would work I think)

Period 01
Period 02
Period 03 etc

The code that I use for this data is:

USE Occupancy
CREATE TABLE Time2
(Date date not null,
CalendarYear int not null,
CalendarMonth varchar (30) not null,
FinancialYear int not null,
FinancialMonth varchar (30) not null)

DECLARE
@StartDate Date,
@EndDate Date

SET @StartDate = '01 Jan 2010'
SET @EndDate = '31 Mar 2015'

WHILE @StartDate <= @EndDate

BEGIN

INSERT INTO Time2(Date,CalendarYear,CalendarMonth,FinancialYear,FinancialMonth)

SELECT
@StartDate Date,YEAR(@StartDate) AS CalendarYear,
DATENAME(MONTH,@StartDate) AS CalendarMonth,
YEAR(Dateadd(MONTH,-3,@StartDate)) AS FinancialYear,
'Period ' + CAST (MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)) AS FinancialMonth
SET @StartDate = DATEADD(dd,1,@StartDate)

END

Can you please let me know what to do next because I haven't got the foggiest.

Thanks

Wayne

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-02-07 : 12:19:55

use right('00'+ CAST (MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)),2)

or

replace(str(MONTH(Dateadd(MONTH, -3,@StartDate))),' ','0')

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-02-07 : 20:18:28

Ordering is a function of the output not the input, so messing around with your data is not generally the solution. However in this case you have the type wrong. You need to store your years and months as integers not strings (which is what is messing you up) and sort on those, not the string.

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-08 : 04:56:43

[quote]Originally posted by stepson

use right('00'+ CAST (MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)),2)

or

replace(str(MONTH(Dateadd(MONTH, -3,@StartDate))),' ','0')

That has helped a lot, thank you so much, I also need to do it with the Calendar Month can you help with that?

DATENAME(MONTH,@StartDate) AS CalendarMonth,

Thanks

Wayne

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-02-08 : 06:27:33

Hi,

I don't understand exactly ...
you use DateName function, that return the Month name .Ok. How you wanted to display?

use the same right;

right('00000000000'+ DATENAME(MONTH,@StartDate) ,NumberOfCharacterYouWant)

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-08 : 06:38:09

Hi Stepson

Sorry I am new to SQL so bare with me.

The CalenderMonth column is fine as it is, however when I query the table I get the following list

April
August
December (so on)

What I would like to see is

January
February
March (So on)

And I need to code this is the SQL, I though it would go on my SELECT Statment CalenderMOnth Section

Somewhere here ---> DATENAME(MONTH,@StartDate) AS CalendarMonth,

So the whole Select statement would be:

SELECT
@StartDate Date,YEAR(@StartDate) AS CalendarYear,
DATENAME(MONTH,@StartDate) AS CalendarMonth,
YEAR(Dateadd(MONTH,-3,@StartDate)) AS FinancialYear,
'Period ' + RIGHT('0' + CAST(MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)),2) AS FinancialMonth

SET @StartDate = DATEADD(dd,1,@StartDate)

END

I hope this makes sense and I hope you can help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-02-08 : 07:00:58

Your select should have this order by clause

ORDER BY substring(FinancialMonth,charindex(' ',FinancialMonth)+1,len(FinancialMonth))*1

Madhivanan

Failing to plan is Planning to fail

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-08 : 07:12:22

[quote]Originally posted by madhivanan

Your select should have this order by clause

ORDER BY substring(FinancialMonth,charindex(' ',FinancialMonth)+1,len(FinancialMonth))*1

Hi Madhivanan

Im sorry to ask but where should it be, I am now working on the CalendarMonth problem.

Thanks

Wayne

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-02-08 : 07:20:29

Order by it's a clauses of the select.
So , you add the text (Order by) after your select

something like this:

SELECT
@StartDate Date,YEAR(@StartDate) AS CalendarYear,
DATENAME(MONTH,@StartDate) AS CalendarMonth,
YEAR(Dateadd(MONTH,-3,@StartDate)) AS FinancialYear,
'Period ' + RIGHT('0' + CAST(MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)),2) AS FinancialMonth
ORDER BY substring(FinancialMonth,charindex(' ',FinancialMonth)+1,len(FinancialMonth))*1

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-08 : 07:29:03

I keep getting the following error message:

Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'ORDER'.

Below is my entire string, as you can see the Financial Month is working brilliantly so when I query this table I get Period 01, Period 02, Period 03 instead of Period 1, Period 10, Period 11. So when I query the Table for Calendar Month I want to see January, February and not April,August. I know I can change the query but I have been told to hardcode like the Financial Month Section below.

USE Occupancy
CREATE TABLE Time3
(Date date not null,
CalendarYear int not null,
CalendarMonth varchar (30) not null,
FinancialYear int not null,
FinancialMonth varchar (30) not null)

DECLARE
@StartDate Date,
@EndDate Date

SET @StartDate = '01 Jan 2010'
SET @EndDate = '31 Mar 2015'

WHILE @StartDate <= @EndDate

BEGIN
INSERT INTO Time3(Date,CalendarYear,CalendarMonth,FinancialYear,FinancialMonth)

SELECT
@StartDate Date,YEAR(@StartDate) AS CalendarYear,
DATENAME(MONTH,@StartDate) AS CalendarMonth,
YEAR(Dateadd(MONTH,-3,@StartDate)) AS FinancialYear,
'Period ' + RIGHT('0' + CAST(MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)),2) AS FinancialMonth

SET @StartDate = DATEADD(dd,1,@StartDate)

END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-02-08 : 07:44:37

Forgot about how they are stored in the table. After you added all data, just use this

SELECT * FROM Time3
ORDER BY substring(FinancialMonth,charindex(' ',FinancialMonth)+1,len(FinancialMonth))*1

Madhivanan

Failing to plan is Planning to fail

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-08 : 07:49:05

[quote]Originally posted by madhivanan

Forgot about how they are stored in the table. After you added all data, just use this

SELECT * FROM Time3
ORDER BY substring(FinancialMonth,charindex(' ',FinancialMonth)+1,len(FinancialMonth))*1

Hi Madhivanan

But its in the table that I want the sort, like my previous post where we sorted the Financial Month by Period I want to do the same for the Calendar Month.

Thanks

Wayne

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-02-08 : 08:16:51

Have you run the above code?

Madhivanan

Failing to plan is Planning to fail

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-08 : 08:24:15

quote:
Originally posted by madhivanan

Have you run the above code?

Madhivanan

Failing to plan is Planning to fail

Hi Madhivanan

The above code works fine if you just want to query the data, but like I have said its not what I need.

Ta

Wayne

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources