StoredprocedurHow to subtact previous value from recent value that value displayed in another column

Posted by Parcha under Sql Server on 7/25/2011 | Points: 10 | Views : 783 | Status : [Member] | Replies : 2
How to subtact previous value from recent value that value displayed in another column
in my database i have 4 columns
actually my task is differnt so iam taking some example
i have to insert data like
EmpID,EMpname,Sal,Expectedsal

when i insert data first like sal 300
the table should be
Empid,name,sal,expsal
1 k 300 0
again wheni insert data like sal 500
table should be
EMpid,name,sal,expsal
1 k 500 0
2 k 300 200
if their more than 500 records how to write storedprocedure
plz help me




Responses

Posted by: Parcha on: 7/25/2011 [Member] Starter | Points: 25

Up
0
Down
any body their plz help me

Parcha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gsudhesh on: 7/26/2011 [Member] Starter | Points: 25

Up
0
Down
try the below SQL snippet .i hope that it will work as per your requirement...

create table empsal
(
id int identity(1,1),
ename varchar(20),
sal numeric(10),
expsal numeric(10)
)


create procedure spEmpInsert

@ename varchar(20),
@sal numeric(18,3)

as
BEGIN

declare @expSal as numeric(18,3)

--getting previous sal for the Coresspoding employee
select top 1 @expSal=sal from empsal where ename=@ename order by id desc

insert into empsal(ename,sal,expsal) values(@ename,@sal,@sal-@expSal)
-- print @expSal
END

Parcha, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response