How to sort MonthName (varchar) in SQL Server? -


i got stuck while sorting month name in order in below query. here period alias user can choose if wants see result in daily, weekly, monthly or yearly. sorting working rest of cases except month. taking month string , if try convert month giving exception.

please have , guide me.

it begin return like:

april august december

desired output:

january february march april december

code:

declare @period char = 'm' declare @finaldateid int = 20170101;  select     period, sum(cast(totalamount bigint)) value                 (select                  case              when @period = 'd'                 cast(d.datename varchar(50))              when @period = 'w'                 cast(d.weekofyear varchar(50))             when @period = 'm'                 cast(d.monthname varchar(50))              when @period = 'y'                 cast(d.calendaryear varchar(50))           end period,           tr.totalamount                revenue tr       inner join            dates d on tr.dateid = d.id                       (tr.dateid between                      case                          when @period = 'd'                             convert(varchar, convert(datetime, convert(char(8), @finaldateid)) - 30, 112)                          when @period = 'w'                             convert(varchar, convert(datetime, convert(char(8), @finaldateid)) - 364, 112)                          when @period = 'm'                             convert(varchar, convert(datetime, convert(char(8), @finaldateid)) - 365, 112)                          when @period = 'y'                             convert(varchar, convert(datetime, convert(char(8), @finaldateid)) - 3652, 112) end , @finaldateid)           ) totalrevenue group      period order     case         when @period = 'm'            convert(month, period)      end

output returning without sort:

period     value -------------------- april         4750 august        4750 december    187250 february      4000 january      12500

desired output after sorting month:

period      value  ----------------------- january     12500 february     4000 april        4750 august       4750 december   187250

shared sample data/table @ https://1drv.ms/u/s!ak02jpvbvozehy0hs2r8rxh6mhsr

don't mess around names of date's part!

any approach build full date using month's name , date fragment in order month's number fail, if executing system has got different language. if application might ever run in international environment, must find way deal without hacks.

if period real date somewhere, might extract year , month using year() , month() , order both of them. both come numeric index.

but solution seems simpler:

obviously using dates table (which thing!)

from posted example take, column [monthofyear] has got numeric month index. , you've got fiscalyear too. include these columns query , use order fiscalyear,monthofyear.


Comments

Post a Comment

Popular posts from this blog

python - Operations inside variables -

this question has answer here: evaluating mathematical expression in string 12 answers i want make python script finds maximum possible output using 2 operations. problem says there syntax error @ 2nd last line, in b in there. how can fix it? x = 0 = int(input("1st number:")) c = int(input("2nd number:" )) e = int(input("3rd number:" )) in range(4): if == 0: b = "+" elif == 1: b = "-" elif == 2: b = "/" else: b = "*" j in range(4): if j == 0: d = "+" elif j == 1: d = "-" elif j == 2: d = "/" else: d = "*" k = b c d e print(k) if you're after variables e on single line, couldn...
Read more

Generic Map Parameter java -

i having lot of methods of type: public static list<eduusers> getdetails(class c, map<string, integer> params) { list<eduusers> details = null; session session = hibernateutil.getsessionfactory().opensession(); transaction tx = null; try { tx = session.begintransaction(); criteria cr = session.createcriteria(c); (map.entry<string, integer> entry : params.entryset()) { cr.add(restrictions.eq(entry.getkey(), entry.getvalue())); } details = cr.list(); tx.commit(); } catch (exception asd) { log.debug(asd.getmessage()); if (tx != null) { tx.commit(); } } { session.close(); } return details; } i trying have generic method them , have written far: public static <t> list<t> getdetails(class<t> c, class<t> b) { l...
Read more

arrays - What causes a java.lang.ArrayIndexOutOfBoundsException and how do I prevent it? -

what arrayindexoutofboundsexception mean , how rid of it? here code sample triggers exception: string[] name = {"tom", "dick", "harry"}; for(int = 0; i<=name.length; i++) { system.out.print(name[i] +'\n'); } your first port of call should documentation explains reasonably clearly: thrown indicate array has been accessed illegal index. index either negative or greater or equal size of array. so example: int[] array = new int[5]; int boom = array[10]; // throws exception as how avoid it... um, don't that. careful array indexes. one problem people run thinking arrays 1-indexed, e.g. int[] array = new int[5]; // ... populate array here ... (int index = 1; index <= array.length; index++) { system.out.println(array[index]); } that miss out first element (index 0) , throw exception when index 5. valid indexes here 0-4 inclusive. correct, idiomatic for statement here be: for (int index = 0; index <...
Read more