Thanks for the question, Prash.
Asked: April 17, 2007 - 7:38 am UTC
Last updated: April 17, 2007 - 11:39 am UTC
Version: 9.1.2
Viewed 1000+ times
You Asked
Hi Tom,
I have a table emp like below
create table emp(ename varchar2(100),
empno number,
deptno number,
salary number,
join_date date,
address varchar2(100),
city varchar2(20),
dob date
);
I want to have a report which can give me details of sum of employee's salary per department based on Join date. The o/p should be like below
DeptNo Salary1 salary2 salary3
Salary1 --> Join_date < Sysdate - 1
Salary2 --> Join_date < Sysdate - 10
Salary3 --> Join_date < Sysdate - 30
I tried to achieve using Union having same query 3 times as below. But that is not gud at performance.
select deptno, sum(salary)
from emp where join_date < sysdate - 1
union
select deptno, sum(salary)
from emp where join_date < sysdate - 10
union
select deptno, sum(salary)
from emp where join_date < sysdate - 30
;
Plz advise.
Rgds,
Prash
and Tom said...
ops$tkyte%ORA10GR2> create table emp
2 as
3 select empno, deptno, sal, sysdate-rownum*3 join_date
4 from scott.emp;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select deptno, sal,
2 case when join_date < sysdate-1 then '*' end sal1,
3 case when join_date < sysdate-10 then '*' end sal10,
4 case when join_date < sysdate-30 then '*' end sal30
5 from emp
6 order by deptno, join_date
7 /
DEPTNO SAL S S S
---------- ---------- - - -
10 1300 * * *
10 5000 * *
10 2450 * *
20 3000 * * *
20 1100 * * *
20 3000 * *
20 2975 * *
20 800 *
30 950 * * *
30 1500 * *
30 2850 * *
30 1250 * *
30 1250 *
30 1600 *
14 rows selected.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select deptno,
2 sum( case when join_date < sysdate-1 then sal end ) sal1,
3 sum( case when join_date < sysdate-10 then sal end ) sal10,
4 sum( case when join_date < sysdate-30 then sal end ) sal30
5 from emp
6 group by deptno
7 order by deptno
8 /
DEPTNO SAL1 SAL10 SAL30
---------- ---------- ---------- ----------
10 8750 8750 1300
20 10875 10075 4100
30 9400 6550 950
3 rows selected.
ops$tkyte%ORA10GR2> with data
2 as
3 (
4 select deptno,
5 sum( case when join_date < sysdate-1 then sal end ) sal1,
6 sum( case when join_date < sysdate-10 then sal end ) sal10,
7 sum( case when join_date < sysdate-30 then sal end ) sal30
8 from emp
9 group by deptno
10 ),
11 pivot as
12 (select level l from dual connect by level <= 3)
13 select deptno, l, decode( l, 1, sal1, 2, sal10, 3, sal30 ) sal
14 from data, pivot
15 order by deptno, l
16 /
DEPTNO L SAL
---------- ---------- ----------
10 1 8750
10 2 8750
10 3 1300
20 1 10875
20 2 10075
20 3 4100
30 1 9400
30 2 6550
30 3 950
9 rows selected.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment