DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: urgent..how to get date from scott.emp table

  1. #1
    Join Date
    Jul 2001
    Posts
    9
    urgent..how to get the date from the emp table...

    Hi,
    I am using the scott.emp table for this.

    I am using this select statement to get dates from the scott.emp table.

    SELECT
    count(r.empno) ,
    to_char(r.hiredate, 'dd.mm.yyyy') as hiredate
    FROM emp r
    WHERE (r.hiredate >= to_date('20.02.1981','dd.mm.yyyy'))
    AND (r.hiredate <= to_date('25.02.1981','dd.mm.yyyy'))
    GROUP BY to_char(r.hiredate, 'dd.mm.yyyy')
    ORDER BY to_char(r.hiredate, 'dd.mm.yyyy')

    The output is

    COUNT(R.EMPNO) HIREDATE
    ------------------------
    1 20.02.1981 empno : 7499
    2 22.02.1981 empno: 7521


    But i want something else,
    If u see the query i have given the date range as
    >= 20.02.1981 and <= 25.02.1981

    So, if there are no records for any date between this range, then it should give me output as 0 and then print the date for which count is 0

    So the output i want is

    COUNT(R.EMPNO) HIREDATE
    ------------------------
    1 20.02.1981
    0 21.02.1981
    2 22.02.1981
    0 23.02.1981
    0 24.02.1981
    0 25.02.1981

    How to get the output like this.

    can anyone give me the code..

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    SQL> select sum(cnt), hiredate from
    (
    SELECT
    count(r.empno) cnt, to_char(r.hiredate, 'dd.mm.yyyy') as hiredate
    FROM emp r
    WHERE (r.hiredate >= to_date('20.02.1981','dd.mm.yyyy'))
    AND (r.hiredate <= to_date('25.02.1981','dd.mm.yyyy'))
    GROUP BY to_char(r.hiredate, 'dd.mm.yyyy')
    union
    select 0, to_char(to_date('20.02.1981','dd.mm.yyyy')+(rownum-1),'dd.mm.yyyy')
    from all_objects
    where
    rownum <= to_date('25.02.1981','dd.mm.yyyy') - to_date('20.02.1981','dd.mm.yyyy') + 1
    )
    group by hiredate;

    SUM(CNT) HIREDATE
    ---------- ----------
    1 20.02.1981
    0 21.02.1981
    1 22.02.1981
    0 23.02.1981
    0 24.02.1981
    0 25.02.1981

    6 rows selected.

    if u want ho have explanation of this select see ur previous post.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width