Home » SQL & PL/SQL » SQL & PL/SQL » calc sanctions to employee
| calc sanctions to employee [message #562466] |
Wed, 01 August 2012 10:24  |
 |
hassan08
Messages: 77
Registered: June 2011
Location: egypt
|
Member |
|
|
i have two tables
first table
employees
emp_no pk
emp_name
emp_salary
emp_hiredate
emp_birthdate
-------------------
second table
sanctions
sanc_id
sanc_name
emp_no fk
i need the follwing
if employees take sanctions and his salary 1000 and sanctions value 50 then
the net salary is 950
i need if the same employee take another sanctions values 50
the result will be
the old net salary is 950
and the new old salary is 900
|
|
|
|
|
|
| Re: calc sanctions to employee [message #562487 is a reply to message #562473] |
Wed, 01 August 2012 11:42  |
 |
Littlefoot
Messages: 16955
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Current salaries in department 10 (Scott's schema):
SQL> select empno, ename, sal
2 from emp
3 where deptno = 10
4 order by ename;
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
The SANCTIONS table:SQL> create table sanctions
2 (empno number,
3 sanction number
4 );
Table created.
Insert a record for KING and calculate new salaries:SQL> insert into sanctions values (7839, 50);
1 row created.
SQL> select e.empno, e.ename, e.sal, e.sal - nvl(sum(s.sanction), 0) new_salary
2 from emp e,
3 sanctions s
4 where e.empno = s.empno (+)
5 and e.deptno = 10
6 group by e.empno, e.ename, e.sal
7 order by e.ename;
EMPNO ENAME SAL NEW_SALARY
---------- ---------- ---------- ----------
7782 CLARK 2450 2450
7839 KING 5000 4950
7934 MILLER 1300 1300
SQL> -- Yet another sanction for King
SQL> insert into sanctions values (7839, 950);
1 row created.
SQL> select e.empno, e.ename, e.sal, e.sal - nvl(sum(s.sanction), 0) new_salary
2 from emp e,
3 sanctions s
4 where e.empno = s.empno (+)
5 and e.deptno = 10
6 group by e.empno, e.ename, e.sal
7 order by e.ename;
EMPNO ENAME SAL NEW_SALARY
---------- ---------- ---------- ----------
7782 CLARK 2450 2450
7839 KING 5000 4000
7934 MILLER 1300 1300
SQL>
Is this what you are looking for?
|
|
|
|
| Re: calc sanctions to employee [message #562749 is a reply to message #562487] |
Sat, 04 August 2012 07:43 |
|
hassan08
Messages: 77
Registered: June 2011
Location: egypt
|
Member |
|
|
thank you the code work ok but i need code work by this step
if fixed salary 1000 and employee take sanction 50 the net salary will be 950 and we are now in month august if month august
end will calc the next month 1000 and calc each month is separate from the other
table structure
second table
sanctions
sanc_id
sanc_name
san_date
emp_no fk
[Updated on: Sat, 04 August 2012 07:46] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: calc sanctions to employee [message #562926 is a reply to message #562466] |
Mon, 06 August 2012 19:11 |
|
hassan08
Messages: 77
Registered: June 2011
Location: egypt
|
Member |
|
|
I have tow tables
first table
employee
employee_id pk
employee_name
employee_birthdate
employee_hiredate
employee_salary
second table
sanction_id pk
sanction_name
sanction_value
sanction_date
sanction_reson
employee_salary
employee_id fk
insert into employee values ('1','ali','1-1-1990'-'1-1-2012','1000');
insert into sanction values('1','mistake','50','1-4-2012','error','1000','1');
insert into sanction values('1','mistake','50','1-4-2012','error','1000','1');
insert into sanction values('1','mistake','50','1-4-2012','error','1000','1');
now i i want calc the net salary after sanction will be
by the previues code will be 950 okay then
if the employee take another scantion in same month
will be 900 then if same employee take another scantion in the month will be net salary
850
my mean if the employee take the new scantion but take it in the anohter month
insert into sanction values('1','mistake','50','1-5-2012','error','1000','1');
will calc on the fixed salary 1000 not the 850 net salary and the result will be
1000 - 50 = 950
------------------------------------------------------------------------------------
if the same employee take a new sanction in the same month value 50
insert into sanction values('1','mistake','50','1-5-2012','error','1000','1');
the result will be 950 - 50 = 900
-------------------------------------------------------------------------------------
but if the same employee take another sanction but take it in the new month
insert into sanction values('1','mistake','50','1-6-2012','error','1000','1');
the result will be 1000 fixed salary - 50 = the result will be 950
-------------------------------------------------------------------------------------
how can make it calc sanction to employee
[Updated on: Mon, 06 August 2012 19:13] Report message to a moderator |
|
|
|
|
|
| Re: calc sanctions to employee [message #562937 is a reply to message #562926] |
Tue, 07 August 2012 01:12 |
|
Littlefoot
Messages: 16955
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Somewhat simplified situation (less columns than your tables):SQL> create table employee
2 (ename varchar2(20),
3 sal number
4 );
Table created.
SQL> create table sanction
2 (ename varchar2(20),
3 sdate date,
4 svalue number
5 );
Table created.
SQL> insert all
2 into employee (ename, sal) values ('LF', 1000)
3 into sanction (ename, sdate, svalue) values ('LF', to_date('15.07.2012', 'dd.mm.yyyy'), 50)
4 into sanction (ename, sdate, svalue) values ('LF', to_date('22.07.2012', 'dd.mm.yyyy'), 100)
5 into sanction (ename, sdate, svalue) values ('LF', to_date('10.08.2012', 'dd.mm.yyyy'), 30)
6 select * from dual;
4 rows created.
SQL> select * from sanction order by sdate;
ENAME SDATE SVALUE
-------------------- ---------- ----------
LF 15.07.2012 50
LF 22.07.2012 100
LF 10.08.2012 30
SQL>
According to what I understood: my monthly salary is 1000. I was a bad employee in July (so I have to subtract 50 + 100 from my fixed salary, which would make 1000 - (50 + 100) = 1000 - 150 = 850) and in August (1000 - 30 = 970).
MONTHS (in a code that follows) simulate several months in a year.
SQL> with months as
2 (select to_date('01.06.2012', 'dd.mm.yyyy') mon from dual union
3 select to_date('01.07.2012', 'dd.mm.yyyy') mon from dual union
4 select to_date('01.08.2012', 'dd.mm.yyyy') mon from dual union
5 select to_date('01.09.2012', 'dd.mm.yyyy') mon from dual
6 )
7 select
8 to_char(m.mon, 'month yyyy') mon,
9 e.ename,
10 e.sal - nvl(sum(s.svalue), 0) new_sal
11 from employee e,
12 months m,
13 sanction s
14 where trunc(m.mon, 'mm') = trunc(s.sdate (+), 'mm')
15 group by m.mon, e.ename, e.sal
16 order by m.mon;
MON ENAME NEW_SAL
-------------- -------------------- ----------
june 2012 LF 1000
july 2012 LF 850
august 2012 LF 970
september 2012 LF 1000
SQL> |
|
|
|
| Re: calc sanctions to employee [message #562996 is a reply to message #562466] |
Tue, 07 August 2012 12:34 |
|
hassan08
Messages: 77
Registered: June 2011
Location: egypt
|
Member |
|
|
before executing the code
(select to_date('01.06.2012', 'dd.mm.yyyy') mon from dual union
3 select to_date('01.07.2012', 'dd.mm.yyyy') mon from dual union
4 select to_date('01.08.2012', 'dd.mm.yyyy') mon from dual union
5 select to_date('01.09.2012', 'dd.mm.yyyy') mon from dual
6 )
7 select
8 to_char(m.mon, 'month yyyy') mon,
9 e.ename,
10 e.sal - nvl(sum(s.svalue), 0) new_sal
11 from employee e,
12 months m,
13 sanction s
14 where trunc(m.mon, 'mm') = trunc(s.sdate (+), 'mm')
15 group by m.mon, e.ename, e.sal
16 order by m.mon;
are created another table to months
create table months(
month_id number,
month_name varchar2(20));
and finally you can give me the table structure of months
and will try and tell you the results
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue May 21 02:28:10 CDT 2013
Total time taken to generate the page: 0.25965 seconds
|
] 
Members
Search
Help
Register
Login
Home
