| Author |
 Topic  |
|
|
wafw1971
Yak Posting Veteran
68 Posts |
 Posted - 02/07/2013 : 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
Yak Posting Veteran
Romania
85 Posts |
Posted - 02/07/2013 : 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 |
Edited by - stepson on 02/07/2013 12:26:18 |
 |
|
|
LoztInSpace
Aged Yak Warrior
876 Posts |
Posted - 02/07/2013 : 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
68 Posts |
Posted - 02/08/2013 : 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
Yak Posting Veteran
Romania
85 Posts |
Posted - 02/08/2013 : 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
68 Posts |
Posted - 02/08/2013 : 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
India
22460 Posts |
Posted - 02/08/2013 : 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
68 Posts |
Posted - 02/08/2013 : 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
Yak Posting Veteran
Romania
85 Posts |
Posted - 02/08/2013 : 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
68 Posts |
Posted - 02/08/2013 : 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
India
22460 Posts |
Posted - 02/08/2013 : 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
68 Posts |
Posted - 02/08/2013 : 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
India
22460 Posts |
Posted - 02/08/2013 : 08:16:51
|
Have you run the above code?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/08/2013 : 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 |
|
|
| |
Topic |
|
|
|
Order/Sort By
