I have emp table with the data below.(my empno is not a primarykey, I have some other field as primary key)

select empno,ename,job,hiredate,deptno from emp;

     EMPNO ENAME      JOB       HIREDATE      DEPTNO
---------- ---------- --------- --------- ----------
      7369 SMITH      CLERK     17-DEC-80         20
      7499 ALLEN      SALESMAN  20-FEB-81         30
      7521 WARD       SALESMAN  22-FEB-81         30
      7566 JONES      MANAGER   02-APR-81         20
      7654 MARTIN     SALESMAN  28-SEP-81         30
      7698 BLAKE      MANAGER   01-MAY-81         30
      7782 CLARK      MANAGER   09-JUN-81         10
      7788 SCOTT      ANALYST   19-APR-87         20
      7839 KING       PRESIDENT 17-NOV-81         10
      7844 TURNER     SALESMAN  08-SEP-81         30
      7876 ADAMS      CLERK     23-MAY-87         20

     EMPNO ENAME      JOB       HIREDATE      DEPTNO
---------- ---------- --------- --------- ----------
      7900 JAMES      CLERK     03-DEC-81         30
      7902 FORD       ANALYST   03-DEC-81         20
      7934 MILLER     CLERK     23-JAN-82         10
      7988 STEVE      CLERK     23-JAN-07         10
      8212 BRADY      ANALYST   23-JAN-06         10
      9133 WILLIAM    CLERK     23-JAN-08         20
      7902 FORD       ANALYST   03-DEC-81         20
      7521 WARD       SALESMAN  22-FEB-81         30

19 rows selected.

empno 7902 and 7521 are repeated in the above output.so when
I execute the query below then those empnos should be counted once only.
The query below is counting twice.

SELECT  period_start_date,SUM(DECODE(deptno, 10, 1, 0)) AS v1
       , SUM(DECODE(deptno, 20, 1, 0)) AS val2
       , SUM(DECODE(deptno, 30, 1, 0)) AS val3
FROM     scott.emp emp
       , (SELECT ADD_MONTHS ( TO_DATE('1980-10-01', 'YYYY-MM-DD')
                            , 6 * (LEVEL - 1)
                            ) AS period_start_date
          FROM   DUAL
          CONNECT BY LEVEL <= 100) dates
WHERE    emp.hiredate BETWEEN dates.period_start_date
                          AND ADD_MONTHS (dates.period_start_date, 6) and
hiredate <= sysdate
GROUP BY period_start_date


output of above query is

PERIOD_ST         V1       VAL2       VAL3
--------- ---------- ---------- ----------
01-OCT-80          0          1          3
01-APR-81          1          1          3
01-OCT-81          2          2          1
01-APR-87          0          2          0

In the above output 1st row val3 count is 3,but it should be 2 because 7521 is repeated so it should be counted only once.
3rd row also value of val2 is 2 as 7902 empno is twice,but it should be counted only once.
my output should be as below

PERIOD_ST         V1       VAL2       VAL3
--------- ---------- ---------- ----------
01-OCT-80          0          1          2
01-APR-81          1          1          3
01-OCT-81          2          1          1
01-APR-87          0          2          0

thanks

SELECT   period_start_date,
         SUM (DECODE (deptno, 10, 1, 0)) AS v1,
         SUM (DECODE (deptno, 20, 1, 0)) AS val2,
         SUM (DECODE (deptno, 30, 1, 0)) AS val3
    FROM (SELECT DISTINCT empno,
                          deptno,
                          hiredate
                     FROM emp) emp,
         (SELECT     ADD_MONTHS (TO_DATE ('1980-10-01', 'YYYY-MM-DD'),
                                 6 * (LEVEL - 1)
                                ) AS period_start_date
                FROM DUAL
          CONNECT BY LEVEL <= 100) dates
   WHERE emp.hiredate BETWEEN dates.period_start_date
                          AND ADD_MONTHS (dates.period_start_date, 6)
     AND hiredate <= SYSDATE
GROUP BY period_start_date
ORDER BY period_start_date;

Hi,

my previous requirement was as the query below

SELECT   period_start_date,
         SUM (DECODE (deptno, 10, 1, 0)) AS v1,
         SUM (DECODE (deptno, 20, 1, 0)) AS val2,
         SUM (DECODE (deptno, 30, 1, 0)) AS val3
    FROM (SELECT DISTINCT empno,
                          deptno,
                          hiredate
                     FROM emp) emp,
         (SELECT     ADD_MONTHS (TO_DATE ('1980-10-01', 'YYYY-MM-DD'),
                                 6 * (LEVEL - 1)
                                ) AS period_start_date
                FROM DUAL
          CONNECT BY LEVEL <= 100) dates
   WHERE emp.hiredate BETWEEN dates.period_start_date
                          AND ADD_MONTHS (dates.period_start_date, 6)
     AND hiredate <= SYSDATE
GROUP BY period_start_date;

the output of above query is

PERIOD_ST         V1       VAL2       VAL3
--------- ---------- ---------- ----------
01-OCT-80          0          1          2
01-APR-81          1          1          3
01-OCT-81          2          1          1
01-APR-87          0          2          0
01-OCT-05          1          0          0
01-OCT-06          1          0          0

6 rows selected.

Now my emp table contains the data as below
EMPNO is not primary key, I have some other field as primary key.

     EMPNO ENAME      JOB       HIREDATE      DEPTNO GRADE GRADEON
---------- ---------- --------- --------- ---------- ----- ---------
      7521 WARD       SALESMAN  22-FEB-81         30 1     22-FEB-81
      7902 FORD       ANALYST   03-DEC-81         20 2     10-OCT-89
      7902 FORD       MANAGER   03-DEC-81         20 3     23-NOV-95
      7902 FORD       MANAGER   03-DEC-81         20 3     23-NOV-95
      7902 FORD       CLERK     03-DEC-81         20 1     03-DEC-81
      7521 WARD       SALESMAN  22-FEB-81         30 1     22-FEB-81

6 rows selected.

my expected output is

PERIOD_ST         V1       VAL2       VAL3
--------- ---------- ---------- ----------
01-OCT-80          0          0          0
01-APR-81          0          0          0
01-OCT-81          0          1          0
01-APR-87          0          0          0
01-OCT-05          0          0          0
01-OCT-06          0          0          0

6 rows selected.

my table data is showing grade and gradeon date fields along with other fields,if the grade changes only then it should be counted as 1 else 0.
employee WARD grade is not changed though WARD record is twice.
so it should be counted as 0.
WARD's hiredate falls under range 01-OCT-80 and 01-APR-81 which is 1st row and deptno is 30.
so 1st row third coulmn value is 0.

FORD grade has been changed twice,so count is one only 
though changed twice and it falls under period_start_date range
01-OCT-81 to 03-MAR-82, which is row 3 in above data.
Gradeon is the date on which grade is awarded to that employee.

thanks