SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Order/Sort By
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/07/2013 :  11:49:34  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 02/07/2013 :  12:19:55  Show Profile  Reply with Quote
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

Edited by - stepson on 02/07/2013 12:26:18
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 02/07/2013 :  20:18:28  Show Profile  Reply with Quote
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.
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/08/2013 :  04:56:43  Show Profile  Reply with Quote
[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
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 02/08/2013 :  06:27:33  Show Profile  Reply with Quote
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
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/08/2013 :  06:38:09  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22777 Posts

Posted - 02/08/2013 :  07:00:58  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/08/2013 :  07:12:22  Show Profile  Reply with Quote
[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
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 02/08/2013 :  07:20:29  Show Profile  Reply with Quote
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
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/08/2013 :  07:29:03  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22777 Posts

Posted - 02/08/2013 :  07:44:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/08/2013 :  07:49:05  Show Profile  Reply with Quote
[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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22777 Posts

Posted - 02/08/2013 :  08:16:51  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Have you run the above code?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/08/2013 :  08:24:15  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000