SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 procedure needed pls help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sunsanvin
Flowing Fount of Yak Knowledge

India
1274 Posts

Posted - 11/21/2006 :  05:30:38  Show Profile  Send sunsanvin a Yahoo! Message  Reply with Quote
Dear Friends,
I have two tables........emp and salgrade

I need a procedure to update emp table with the given % hikes.The grades we should take from salgrade table

if the grade =1, then the hike will be 40%
if the grade =2, then the hike will be 30%
if the grade =3, then the hike will be 20%
if the grade =4, then the hike will be 10%


so whenever i execute this procedure, the table should be automatically updated.



thank you verymuch.


Vinod

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 11/21/2006 :  05:35:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
update       e
set          e.hike = case
                         when s.grade = 1 then 0.4
                         when s.grade = 2 then 0.3
                         when s.grade = 3 then 0.2
                         when s.grade = 4 then 0.1
                         ...
from         emp e
inner join   salgrade s on s.<somecol> = e.<somecol>


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/21/2006 :  05:36:41  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Update e
set col = col +	case sg.grade
			when 1 then col * 0.4
			when 2 then col * 0.3
			when 3 then col * 0.2
			when 4 then col * 0.1
		end
from emp e join salgrade sg
on e.id = sg.id


Edit: oooooooouch !!!




Harsh Athalye
India.
"Nothing is Impossible"

Edited by - harsh_athalye on 11/21/2006 05:38:01
Go to Top of Page

sunsanvin
Flowing Fount of Yak Knowledge

India
1274 Posts

Posted - 11/21/2006 :  05:39:08  Show Profile  Send sunsanvin a Yahoo! Message  Reply with Quote
Thank you peso and harsha
thank you very much

Vinod
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 11/21/2006 :  05:41:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If you want some read-only code,
update       e
set          e.hike = (5 - s.grade) / 10
from         emp e
inner join   salgrade s on s.<somecol> = e.<somecol>
where        s.grade between 1 and 4


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 11/21/2006 :  11:40:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Peso

If you want some read-only code,
update       e
set          e.hike = (5 - s.grade) / 10
from         emp e
inner join   salgrade s on s.<somecol> = e.<somecol>
where        s.grade between 1 and 4


Peter Larsson
Helsingborg, Sweden


If both are integers wont that give 0 for 3/10, 4/10, etc?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 11/21/2006 :  12:16:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes.
Easily fixed by dividing by 10.0


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 11/22/2006 :  11:22:21  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
or by (5 - s.grade)*1.0 / 10


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000