Home » SQL & PL/SQL » SQL & PL/SQL » Case Help
Case Help [message #567528] Wed, 03 October 2012 02:18 Go to next message
srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad
Member
hi all

i want run this query on sql developer any one guide me plzz

select
CASE extract(year from DATE_POLICY_START)
WHEN extract(year from DATE_POLICY_START)='2011'
then DATE_POLICY_START( BETWEEN to_date('01-apr-2011','dd-mon-yyyy') AND to_date('31-mar-2012','dd-mon-yyyy')
else extract(year from DATE_POLICY_START)
,count(*)
from stg
group by CASE extract(year from DATE_POLICY_START)
WHEN extract(year from DATE_POLICY_START)='2011'
then DATE_POLICY_START( BETWEEN to_date('01-apr-2011','dd-mon-yyyy') AND to_date('31-mar-2012','dd-mon-yyyy')
else extract(year from DATE_POLICY_START)

Thanks and regards
Re: Case Help [message #567529 is a reply to message #567528] Wed, 03 October 2012 02:33 Go to previous messageGo to next message
Littlefoot
Messages: 19315
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure that you can run that query anywhere (not only in SQL Developer). It doesn't seem to be a valid query and syntax is somewhat awkward.

Perhaps you should provide a simple test case and explain what you'd like to get as a result.
Re: Case Help [message #567532 is a reply to message #567529] Wed, 03 October 2012 02:45 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
Littlefoot wrote on Wed, 03 October 2012 08:33
I'm not sure that you can run that query anywhere (not only in SQL Developer)..

I'm not even convinced you can call it a query. :/
Re: Case Help [message #567533 is a reply to message #567529] Wed, 03 October 2012 02:46 Go to previous messageGo to next message
srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad
Member
Sorry

i have Column named Date_Start like this
11-Jun-09
22-Nov-10
06-Aug-10
12-Jul-10
05-Nov-10
19-May-10
18-Aug-10
28-Feb-11
14-Nov-10
21-Apr-11
25-May-11
16-Nov-10
15-FEB-12
20-Apr-10
15-Mar-12
25-Aug-10
31-Aug-10
18-Mar-10

in that i want extract Year wise counts

select extract (year from date_start),count(*)
from Table_Name
group by extract(year from date_start)

but
when considering Year '11' i.e, '2011' date_start should be in between 1st april 2011 and 31st march 2012....

Thanks and Regards
Re: Case Help [message #567534 is a reply to message #567533] Wed, 03 October 2012 02:57 Go to previous messageGo to next message
Littlefoot
Messages: 19315
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something like this?
SQL> select * from test order by col;

COL
----------
11.06.2009
18.03.2010
20.04.2010
19.05.2010
12.07.2010
06.08.2010
18.08.2010
25.08.2010
31.08.2010
05.11.2010
14.11.2010
16.11.2010
22.11.2010
28.02.2011
21.04.2011
25.05.2011
15.02.2012
15.03.2012

18 rows selected.

SQL> with ntest as
  2    (select col,
  3            case when to_number(to_char(col, 'mm')) < 4 then to_number(to_char(col, 'yyyy')) - 1
  4                 else to_number(to_char(col, 'yyyy'))
  5            end yr
  6     from test
  7    )
  8  select yr, count(*) cnt
  9  from ntest
 10  group by yr
 11  order by yr;

        YR        CNT
---------- ----------
      2009          2
      2010         12
      2011          4

SQL>
Re: Case Help [message #567662 is a reply to message #567534] Thu, 04 October 2012 02:15 Go to previous messageGo to next message
srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad
Member
Thank u
Re: Case Help [message #567682 is a reply to message #567662] Thu, 04 October 2012 05:54 Go to previous message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
Or something like this:

select count(*), extract(year from add_months(date_start, -3)) 
from stg 
group by extract(year from add_months(date_start, -3))
Previous Topic: PL/SQL block using substitution variable
Next Topic: get the MAX length of the column value along with the value
Goto Forum:
  


Current Time: Sat Jul 26 10:36:28 CDT 2014

Total time taken to generate the page: 0.46029 seconds