|
|
|
|
|
|
|
|
|
|
| Re: Update 7% basic Salary [message #651105 is a reply to message #651094] |
Wed, 11 May 2016 06:52   |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Our colleague Barbara is a marvelous programmer (better than me) and very helpful. To make the job easier, should she choose to assist, this is how I would approach the problem.
First, write a query that will update the rows in emp_payment, applying the salary change to all rows. Can you do that?
Second, write a query that will select from emp_official only those rows for people employed for one yer (one exactly? Or is it a range?). Do that, too.
Third, use the second query as a predicate for the first query.
Job done! Have a go. Note that there may well be a better way to do it.
|
|
|
|
|
|
| Re: Update 7% basic Salary [message #651117 is a reply to message #651106] |
Wed, 11 May 2016 11:06 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
as long as the job is only run once a day the following code will work
update employee
set base_pay = base_pay * 1.07
where hire_date = add_months(trunc(sysdate),-12);
|
|
|
|
|
|
|
|
| Re: Update 7% basic Salary [message #651127 is a reply to message #651094] |
Wed, 11 May 2016 15:07 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Bummer, I didn't think of that. Can do the following
update employee
set base_pay = base_pay * 1.07
where to_char(sysdate,'MMDD') <> '0229'
AND hire_date = add_months(trunc(sysdate),-12);
Thank you very much for the catch.
|
|
|
|
|
|
|
|
| Re: Update 7% basic Salary [message #651271 is a reply to message #651130] |
Sat, 14 May 2016 01:31 |
 |
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
|
Member |
|
|
WOWOWOW!!!
I m really impressed that many of my bro/sis thinking about my matter......I tried on my matter that u gave me...
1st I failed to convert my Basic salary to Gross. Here some example that we convert Basic to Gross
Gross After 7% Increment on Basic new gross
5300 5510
6300 6560
6100 6350
6420 6686
6500 6770
Converting Gross to Basic is =(gross - 1100)/1.4
I again say I m newcomer on Oracle & this forum also please try to give more details..
basically our company recruit worker within 10th working day.. so dont get bother about 29th Feb....
Again thanx in advance...
[Updated on: Sat, 14 May 2016 01:41] Report message to a moderator |
|
|
|
| Re: Update 7% basic Salary [message #651276 is a reply to message #651271] |
Sat, 14 May 2016 02:43 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:.I tried on my matter that u gave me...
1st I failed to convert my Basic salary to Gross. What did you try? Show the SQL, and the result.
By the way, there is a problem with your keyboard: it inserts multiple "...." characters. There is also a problem with your spell checker, it spells "you" as "u", "thanks" as "thanx", and so on. Please correct these issues. Using that sort of thing in a professional forum is poor etiquette, and may cause problems for the many people here who are not native English speakers.
|
|
|
|
| Re: Update 7% basic Salary [message #651277 is a reply to message #651276] |
Sat, 14 May 2016 04:24 |
|
Salehin
Messages: 62
Registered: March 2013
Location: Chittagong, Bangladesh
|
Member |
|
|
SQL> update emp_payment
2 set gross = gross * 1.07
3 where joining_date = add_months(trunc(sysdate),-12);
here joining_date = add_months(trunc(sysdate),-12)
*
RROR at line 3:
RA-00904: "JOINING_DATE": invalid identifier
I think my emp_payment table dont have any joining_date column.
joining_date is in emp_official table
[Updated on: Sat, 14 May 2016 04:30] Report message to a moderator |
|
|
|
| Re: Update 7% basic Salary [message #651295 is a reply to message #651277] |
Sat, 14 May 2016 19:47 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your entire problem is unclear. I don't even know if an update statement is what you need. It may be that you would be better off including the computation in a select statement that just uses "joining_date<=add_months(sysdate,-12)", so it would apply the formula for your computation to everybody that was hired more than a year ago. That would eliminate the need for any updates. I assume that you would use such selects for any payment schedules to be made, something like:
SELECT emp_payment.empno,
CASE WHEN emp_official.joining_date <= ADD_MONTHS (SYSDATE, -12)
THEN emp_payment.gross * 1.7 -- or whatever your actual formula is
ELSE emp_payment.gross
END gross
FROM emp_payment, emp_official
WHERE emp_payment.empno = emp_official.empno;
Your formulas are unclear. You have only gross stored in your table. You have a formula for converting gross to basic (gross1100)/1.4. You say that you need to give a 7% raise on basic (basic*1.7) and yet you are applying the 1.7 to gross, not basic, as if you are under the misimpression that the results will be the same. You posted some sample data for gross and new gross after 7% increment on basic, but it doesn't match your formulas. You need to post a clear explanation with matching examples, step by step, of how you mathematically convert old gross to new gross.
If you do decide to do manual updates for the first 10 days of every month, then it will be something like:
UPDATE emp_payment
SET gross = gross * 1.7 -- or whatever your actual formula is
WHERE empno IN
(SELECT empno
FROM emp_official
WHERE TRUNC (joining_date) = ADD_MONTHS (TRUNC (SYSDATE), -12));
|
|
|
|
|
|
|
|
|
|
| Re: Update 7% basic Salary [message #651339 is a reply to message #651335] |
Mon, 16 May 2016 01:56 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:I want the whole month joining_date will be update. Your subquery (or rather, BB's subquery) uses an equality predicate applie to the default result of the TRUNC functon. The default result strips off the hours-minutes-seconds part of the date. You need to use TRUNC with a non-default format, to strip off the day as well. You can look it up here,
http://docs.oracle.com/database/121/SQLRF/toc.htm
|
|
|
|
|
|
|
|
| Re: Update 7% basic Salary [message #651356 is a reply to message #651094] |
Mon, 16 May 2016 07:37 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
actually do it like this
UPDATE emp_payment p
SET p.gross = p.gross + (((p.gross - 1100) / 1.4) * .07)
WHERE empno IN
(SELECT o.empno
FROM emp_official o
WHERE TRUNC (o.joining_date,'MONTH') = TRUNC(ADD_MONTHS (SYSDATE, -12),'MONTH'));
You would run the update ONCE a month on the first day of the month. This can be done by using dbms_job or dbms_scheduler to automatically run it.
[Updated on: Mon, 16 May 2016 07:38] Report message to a moderator |
|
|
|
| Re: Update 7% basic Salary [message #651357 is a reply to message #651356] |
Mon, 16 May 2016 08:51 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Well, as employee I do like earlier raises but I doubted employers do. Normally it would be first of next month unless employee joined first of the month (in other word first of the month after completing 12 months of service):
UPDATE emp_payment p
SET p.gross = p.gross + (((p.gross - 1100) / 1.4) * .07)
WHERE empno IN
(SELECT o.empno
FROM emp_official o
WHERE TRUNC (o.joining_date,'MONTH') = TRUNC(ADD_MONTHS (SYSDATE, -13),'MONTH')
OR o.joining_date = TRUNC(ADD_MONTHS (SYSDATE, -12),'MONTH')
);
But in real HR system pay is never updated. HR systems are temporal, so new row is inserted ahead of time with proper effective from & to dates and current row effective to date is updated from open ended to day before new pay takes effect. Then payroll will pickup proper pay even if employee submitted time sheet after the raise for time period before the raise.
SY.
|
|
|
|
| Re: Update 7% basic Salary [message #651358 is a reply to message #651357] |
Mon, 16 May 2016 08:56 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
I agree, I have never seen an employee system where there was one row for pay information. every adjustment (location, amount, status...) has a new row with an effective date starting and ending date on each record. You would end the previous record by setting the effective ending date to the day before the change and start the next record on the new effective date. You can also put in a unique key on the employee ID and the effective date any your code would just grab the highest effective date. Up to you. I was just showing a possible method. I assumed this was homework.
[Updated on: Mon, 16 May 2016 08:57] Report message to a moderator |
|
|
|
| Re: Update 7% basic Salary [message #651359 is a reply to message #651352] |
Mon, 16 May 2016 09:14 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Salehin wrote on Mon, 16 May 2016 10:47I have done at last with the help of you. I make some changes have a look to my syntax
UPDATE emp_payment p
SET p.gross = p.gross + (((p.gross - 1100) / 1.4) * .07)
WHERE empno IN
(SELECT o.empno
FROM emp_official o, emp_personal ep
WHERE o.joining_date BETWEEN '01-MAY-2015' AND '30-MAY-2015'
AND O.COMPANY_NAME='Clifton Apparels Ltd'
AND EP.STATUS='Active'
and o.empno=ep.empno)
/
Now I want to take a backup of my Old gross in my emp_payment table on old_gross column. Please help me Does Clifton Apparels Ltd work on the basis that May has only 30 days?
|
|
|
|
| Re: Update 7% basic Salary [message #651360 is a reply to message #651359] |
Mon, 16 May 2016 09:15 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Thats simple, simply
UPDATE emp_payment p
SET p.gross = p.gross + (((p.gross - 1100) / 1.4) * .07),
p.old_gross = p.gross
WHERE empno IN
(SELECT o.empno
FROM emp_official o, emp_personal ep
WHERE o.joining_date BETWEEN '01-MAY-2015' AND '30-MAY-2015'
AND O.COMPANY_NAME='Clifton Apparels Ltd'
AND EP.STATUS='Active'
and o.empno=ep.empno)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Update 7% basic Salary [message #652783 is a reply to message #652774] |
Sun, 19 June 2016 13:55 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I forgot to mention again that you need to use to_date and a format with your date strings, instead of relying on the nls_date_format for the session matching. Otherwise, someday somebody will change the nls_date_format and all of your queries will fail. I keep saying this, but you keep posting the same bad code. So, it should be (assuming that your joining_date is a truncated date):
UPDATE emp_payment p
SET p.gross = p.gross + ROUND(((p.gross - 1100) / 1.4) * .07),
p.old_gross = p.gross
WHERE empno IN
(SELECT o.empno
FROM emp_official o, emp_personal ep
WHERE o.joining_date BETWEEN TO_DATE('01-MAY-2015','DD-MON-YYYY') AND TO_DATE('31-MAY-2015','DD-MON-YYYY')
AND O.COMPANY_NAME='Clifton Apparels Ltd'
AND EP.STATUS='Active'
and o.empno=ep.empno)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
:
:
