CODEWITHSUNDEEP
oracle-database
ravt
ravt

Reputation: 71

formating the date in ascending order

can you please help me to in the following.

I wrote the below query as per the requirement 

SQL> SELECT   mon."months",
  2           COUNT (DECODE (e1.ename, 'RAVI', 1, DECODE (e1.ename, 'KIRAN', 1, NULL))
  3                 ) AS "num-review"
  4      FROM (SELECT     TO_CHAR (ADD_MONTHS (SYSDATE, LEVEL - 7),
  5                                'MON-YYYY'
  6                               ) "months"
  7                  FROM DUAL
  8            CONNECT BY LEVEL <= 18
  9              ORDER BY LEVEL) mon, (select ename, hiredate, to_char(hiredate,'MON-YYYY') "Month" from emp_copy) e1
 10              WHERE mon."months"=e1."Month"(+)
 11  GROUP BY "months";

months   num-review
-------- ----------
APR-2013          0
AUG-2013          0
DEC-2012          1
DEC-2013          0
FEB-2013          2
FEB-2014          0
JAN-2013          1
JAN-2014          0
JUL-2013          0
JUN-2013          0
MAR-2013          0

months   num-review
-------- ----------
MAY-2013          0
NOV-2012          0
NOV-2013          0
OCT-2012          1
OCT-2013          0
SEP-2012          1
SEP-2013          0

18 rows selected.

here i have to get the output like

Sep-2012
Oct-2012
Nov-2012
Dec-2012
Jan-2013
Feb-2013
Mar-2013
Apr-2013
May-2013
Jun-2013
Jul-2013
Aug-2013
Sep-2013
Oct-2013
Nov-2013
Dec-2013
Jan-2014
Feb-2014

but i am getting the output in the different fashion. can any one please help me to fix the problem.

Upvotes: 1

Views: 1538

Answers (2)

Aspirant
Aspirant

Reputation: 2278

If you are sorting the dates according to MON-YYYY it will give give the o/p starting from APR,AUG.. because MON-YYYY will be some thing like APR-2013 and it sorts data alphabetically, So you need to extract also numeric value of the month i.e MM-YYYY along with 'MON-YYYYand sort withMM-YYYYY` then the result will be your expected result i.e dates in sorted manner.

 SELECT mon."months",COUNT (DECODE (e1.ename, 'RAVI', 1, DECODE (e1.ename, 'KIRAN', 1, NULL))) AS "num-review"
 FROM (SELECT TO_CHAR(ADD_MONTHS(SYSDATE, LEVEL - 7),'MON-YYYY') "months",TO_CHAR(ADD_MONTHS(SYSDATE, LEVEL - 7),'MM-YYYY') "months_num"
      FROM DUAL CONNECT BY LEVEL <= 18 ORDER BY LEVEL) mon,
     (select ename, hiredate, to_char(hiredate,'MON-YYYY') "Month" from emp_copy) e1
     WHERE mon."months"=e1."Month"(+)
     GROUP BY "months" order by "months_num";

Upvotes: 1

jas
jas

Reputation: 10865

Because you're using to_char on the date to get "months", ordering by that will just give you the months alphabetically (as you're seeing). You need to give yourself another field that will be sorted chronologically, which you can do with 'YYYYMM', e.g. and order by that. Here's an example based on your query (simplified to remove your specific tables). Does this help?

select "months", count(*) "num", "sort_months" from 
(
SELECT TO_CHAR (ADD_MONTHS (SYSDATE, LEVEL - 7), 'MON-YYYY') "months",
       TO_CHAR (ADD_MONTHS (SYSDATE, LEVEL - 7), 'YYYYMM') "sort_months"
FROM DUAL 
 CONNECT BY LEVEL <= 18
 ORDER BY LEVEL
)
group by "months", "sort_months"
order by "sort_months";

Upvotes: 1

Related Questions