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.
Author |
Topic |
wafw1971
Yak Posting Veteran
75 Posts |
Posted - 2013-02-07 : 11:49:34
|
Evening AllI 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 1Period 10Period 11Period 12Period 2What I would like to see is (just adding a 0 to the first 9 numbers would work I think) Period 01 Period 02Period 03 etcThe code that I use for this data is:USE OccupancyCREATE 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 <= @EndDateBEGIN 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 FinancialMonthSET @StartDate = DATEADD(dd,1,@StartDate)ENDCan you please let me know what to do next because I haven't got the foggiest.ThanksWayne |
|
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 mutsabinWeb |
|
|
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, ThanksWayne |
|
|
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)SCe-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb |
|
|
wafw1971
Yak Posting Veteran
75 Posts |
Posted - 2013-02-08 : 06:38:09
|
Hi StepsonSorry 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 listAprilAugustDecember (so on)What I would like to see is JanuaryFebruaryMarch (So on)And I need to code this is the SQL, I though it would go on my SELECT Statment CalenderMOnth SectionSomewhere 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 FinancialMonthSET @StartDate = DATEADD(dd,1,@StartDate)ENDI 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 clauseORDER BY substring(FinancialMonth,charindex(' ',FinancialMonth)+1,len(FinancialMonth))*1MadhivananFailing 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 clauseORDER BY substring(FinancialMonth,charindex(' ',FinancialMonth)+1,len(FinancialMonth))*1Hi MadhivananIm sorry to ask but where should it be, I am now working on the CalendarMonth problem.ThanksWayne |
|
|
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 selectsomething 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 FinancialMonthORDER BY substring(FinancialMonth,charindex(' ',FinancialMonth)+1,len(FinancialMonth))*1SCe-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb |
|
|
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 15Incorrect 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 OccupancyCREATE 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 <= @EndDateBEGIN 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 FinancialMonthSET @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 thisSELECT * FROM Time3ORDER BY substring(FinancialMonth,charindex(' ',FinancialMonth)+1,len(FinancialMonth))*1MadhivananFailing 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 thisSELECT * FROM Time3ORDER BY substring(FinancialMonth,charindex(' ',FinancialMonth)+1,len(FinancialMonth))*1Hi MadhivananBut 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.ThanksWayne |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-02-08 : 08:16:51
|
Have you run the above code?MadhivananFailing 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?MadhivananFailing to plan is Planning to fail
Hi MadhivananThe above code works fine if you just want to query the data, but like I have said its not what I need.TaWayne |
|
|
|
|
|
|
|