Home » SQL & PL/SQL » SQL & PL/SQL » query problem
query problem [message #251851] Mon, 16 July 2007 22:20 Go to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
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 CLERK 03-DEC-81 20 1 03-DEC-81
7521 WARD SALESMAN 22-FEB-81 30 1 22-FEB-81

5 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 0 0
01-APR-87 0 0 0
...
...
01-OCT-95 0 1 0
01-OCT-05 0 0 0
01-OCT-06 0 0 0


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 gradeon field (date field) falls under range 01-OCT-80 and 01-MAR-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 gradeon field range between
01-OCT-95 and 01-MAR-96.
Gradeon is the date on which grade is awarded to that employee.

thanks
Re: query problem [message #251852 is a reply to message #251851] Mon, 16 July 2007 22:23 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Please read & FOLLOW the posting guidelines as listed in the STICKY posts at the top of this forum; especially the use of "code tags"

[Updated on: Mon, 16 July 2007 22:24] by Moderator

Report message to a moderator

Re: query problem [message #251853 is a reply to message #251851] Mon, 16 July 2007 22:59 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
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          0          0
01-APR-87          0          0          0
...
...
01-OCT-95 	   0          1          0
01-OCT-05 	   0          0          0
01-OCT-06          0          0          0

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 gradeon field (date field) falls under range 01-OCT-80 and 01-MAR-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 gradeon field range between
01-OCT-95 and 01-MAR-96.
Gradeon is the date on which grade is awarded to that employee.

thanks

[Updated on: Tue, 17 July 2007 01:10] by Moderator

Report message to a moderator

Re: query problem [message #251854 is a reply to message #251851] Mon, 16 July 2007 23:26 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
I hope that you admit the 2nd entry is much more readable when compared to the 1st entry.
Now that I can read & understand the problem, I'll confess I don't have a solution.

[Updated on: Mon, 16 July 2007 23:27] by Moderator

Report message to a moderator

Re: query problem [message #251922 is a reply to message #251854] Tue, 17 July 2007 03:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I don't have the scott tables available right now, but the query

select gradeon                             grade_date,
       grade                               grade_new,
       lag(grade) over 
              (partition by ename 
                   order by gradeon)       grade_old
from emp


might give you some idea on how to find out when the grade of an employee changed.
Re: query problem [message #251953 is a reply to message #251851] Tue, 17 July 2007 04:19 Go to previous message
stevefaulk
Messages: 36
Registered: June 2007
Member

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          0          0
01-APR-87          0          0          0
...
...
01-OCT-95 	   0          1          0
01-OCT-05 	   0          0          0
01-OCT-06          0          0          0

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 gradeon field (date field) falls under range 01-OCT-80 and 01-MAR-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 gradeon field range between
01-OCT-95 and 01-MAR-96.
Gradeon is the date on which grade is awarded to that employee.


I tried as 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,gradeon
                     FROM emp) emp,(select empno,grade_rank  from
(select empno,grade,gradeon,rank() over
(partition by empno  order by grade) grade_rank
from emp
)
where grade_rank=2
) emp1 ,
         (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.gradeon BETWEEN dates.period_start_date
                          AND ADD_MONTHS (dates.period_start_date, 6)
     AND emp.gradeon <= SYSDATE and emp1.grade_rank=2 and emp.empno = emp1.empno
GROUP BY period_start_date

I am getting the output as below

PERIOD_ST         V1       VAL2       VAL3
--------- ---------- ---------- ----------
01-OCT-89          0          3          0


but my output should be

PERIOD_ST         V1       VAL2       VAL3
--------- ---------- ---------- ----------
01-OCT-95          0          1          0

I tried using lag as per the previous reply,but not getting the expected result

thanks
Previous Topic: Regarding error : Subscript beyond count
Next Topic: Regarding FORALL ...DELETE
Goto Forum:
  


Current Time: Sun Dec 04 10:26:32 CST 2016

Total time taken to generate the page: 0.08784 seconds