Re: Oracle SQL Query: Help needed, Please.

This may be a bit simpler. The alter session allows the dates to in the correct aorder and print as you desire. Note, I added "rownum" to insure that all of the same dates are counted seperately. The union will do a distinct. The trunc in the date will reduce the date to month/year.

HTH
   James

ALTER SESSION SET nls_date_format='mon-yy' ;

SELECT trunc(c.month_year,'mm'), sum(c.edt) enrollments, sum(c.tdt) terminations

   FROM

     (SELECT ALL enr month_year, 1 edt, 0 tdt, rownum 
         FROM t1 WHERE enr IS NOT NULL
      UNION SELECT ALL ter, 0, 1, rownum FROM t1 WHERE ter IS NOT NULL) c

In article <353DCCCB.FBFE6AB8_at_hotmail.com>, T Suresh kumar <sureshkt_at_hotmail.com> wrote:

>Hai
> Adding to the one I sent yesterday you can avoid view creation by
>replcaing view vt1 like below
> if you have to do it in a single query.,
>
> COLUMN ENR FORMAT A10
>select c.enr, c.c1, d.c2
>from
>(select b.enr enr, count(a.enr) c1
>from tdat a,
> (select to_char(enr,'mon-yy') enr from tdat
> union
> select to_char(ter,'mon-yy') from tdat) b
>where b.enr = to_char(a.enr(+),'mon-yy')
>group by b.enr) c,
>(select b.enr enr, count(a.ter) c2
>from tdat a,
> (select to_char(enr,'mon-yy') enr from tdat
> union
> select to_char(ter,'mon-yy') from tdat) b
>where b.enr = to_char(a.ter(+),'mon-yy')
>group by b.enr) d
>where c.enr = d.enr
>order by substr(c.enr,5,6)
>/
>Regards
>Suresh Kumar
>
>T Suresh kumar wrote:
>
>> Hai
>> Below is the query I could write., I had to write 2 times the same
>>
>> query in from clause
>> because Outer join is not accepted in OR conditions.,
>>
>> COLUMN enr FORMAT A10
>> create view vt1 as (select to_char(enr , 'mon-yy') enr from t1
>> union
>> select to_char(ter,'mon-yy') from
>>
>> t1);
>> select c.enr,c.c1, d.c2
>> from
>> (select vt1.enr enr, count(a.enr) c1
>> from t1 a, vt1
>> where vt1.enr = to_char(a.enr(+), 'mon-yy')
>> group by vt1.enr) c ,
>> (select vt1.enr enr, count(a.ter) c2
>> from t1 a, vt1
>> where vt1.enr = to_char(a.ter(+), 'mon-yy')
>> group by vt1.enr) d
>> where c.enr = d.enr
>> order by substr(c.enr,5,6)
>>
>> I hope this will help you in sorting out the problem.
>>
>> Regards
>> Suresh
>>
>> edawad_at_yahoo.com wrote:
>>
>> > -- I would appriciate your help on this Query:
>> > --
>> > -- The following table is created:
>> > --
>> > create table t1 (
>> > emp_id number(2)
>> > ,enr date
>> > ,ter date
>> > );
>> > --
>> > -- Then the following rows were inserted into that table:
>> > --
>> > insert into t1 values (1, to_date('02-jan-97'),
>> to_date('02-jan-98'));
>> >
>> > insert into t1 values (2, to_date('10-jan-97'),
>> to_date('30-mar-97'));
>> >
>> > insert into t1 values (3, to_date('11-apr-97'),
>> to_date('13-nov-97'));
>> >
>> > insert into t1 values (4, to_date('12-may-97'),
>> to_date('13-nov-97'));
>> >
>> > insert into t1 values (5, to_date('20-nov-97'),
>> to_date('14-jan-98'));
>> >
>> > insert into t1 values (6, to_date('30-jun-97'),
>> to_date('15-feb-98'));
>> >
>> > commit;
>> > /*
>> > I want the following result from a single query:
>> >
>> > month_year enrollments terminations
>> > ---------- ----------- -------------
>> > jan-97 2 0
>> > mar-97 0 1
>> > apr-97 1 0
>> > may-97 1 0
>> > jun-97 1 0
>> > nov-97 1 2
>> > jan-98 0 2
>> > feb-98 0 1
>> >
>> > Thank you in advance.
>> > */
>> >
>> > -----== Posted via Deja News, The Leader in Internet Discussion
>> > ==-----
>> > http://www.dejanews.com/ Now offering spam-free web-based
>> > newsreading

-- 
lorenzen_at_tiny.net             | Life is complex; it has
                              |   real and imaginary parts