StoredProcedure:How to subtact previous value from recent value that value displayed in another column

Question

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

John Penn · Answer

Uh oh... looks like I owe you and Zoran an apology... I didn't understand the requirement correctly and didn't realize the rows were interdependent. In that case you need to follow Zoran's example because you do need to go through the table and find the MAX(empid). New table DDL: CREATE TABLE [dbo].[Karthik]( [Empid] [int] IDENTITY(11) NOT NULL [name] [varchar](50) NOT NULL [sal] [money] NOT NULL [expsal] [money] NOT NULL ) ON [PRIMARY] New proc: ALTER PROCEDURE InsertSalary -- Add the parameters for the stored procedure here @name varchar(50) @newsal money AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @maxempid int DECLARE @lastsal money SELECT @maxempid = MAX(empid) FROM Karthik IF (@maxempid IS NOT NULL) BEGIN SELECT @lastsal = sal FROM Karthik WHERE empid = @maxempid INSERT INTO Karthik (name sal expsal) SELECT @name @lastsal @newsal - @lastsal FROM Karthik WHERE empid = @maxempid UPDATE Karthik SET sal = @newsal WHERE empid = @maxempid END ELSE BEGIN -- this is a new employee record so insert it INSERT INTO Karthik (name sal) VALUES (@name @newsal) -- default value is already set for expsal no need to insert it here END END GO

John Penn · Answer

Just realized I had an extraneous SELECT FROM in the block that gets the last salary entered. We're not actually selecting anything from the table for the insert -- all of the values are already in variables (or calculated from variables). The line that changed is in green below: . . . IF (@maxempid IS NOT NULL) BEGIN SELECT @lastsal = sal FROM Karthik WHERE empid = @maxempid INSERT INTO Karthik (name sal expsal) VALUES (@name @lastsal @newsal - @lastsal) UPDATE Karthik SET sal = @newsal WHERE empid = @maxempid END ELSE . . .

karthik parcha · Answer

hi john thanq for ur patience i will check it now you and zoran helping me a lot thanq you very much

karthik parcha · Answer

hi Actually iam subtracting only column i.e Sal column Example: 1 K 500 0 2 k 300 200 In your procedure you have talen where Expsal=@newsal - sal why you have written like this can u tell plzzzz

John Penn · Answer

If it helps to understand it better this works too: ALTER PROCEDURE InsertSalary -- Add the parameters for the stored procedure here @empid int @name varchar(50) @newsal money AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF EXISTS (SELECT 1 FROM Karthik WHERE empid = @empid) BEGIN -- record already exists for this employee so update it UPDATE Karthik SET expsal = @newsal - sal WHERE Empid = @empid END ELSE BEGIN -- this is a new employee record so insert it INSERT INTO Karthik (Empid name sal) VALUES (@empid @name @newsal) -- default value is already set for expsal no need to insert it here END END GO

karthik parcha · Answer

Hi Awesome Explanation Thanks Karthik

John Penn · Answer

That line is used to determine if a row already exists in the table for that empid. The next line checks the built-in @@ROWCOUNT variable to see how many rows matched empid = @empid. In your case @@ROWCOUNT will be 0 or 1. You can't have a SELECT statement without fields so that's why I have SELECT 1. The 1 is just a dummy constant value. Some people use aggregate functions like COUNT() or MAX() but this is not as efficient because you're forcing the db engine to go through the entire table counting or calculating values when all you really need to know is if the row exists or not.

karthik parcha · Answer

Iam getting error like Delta_contacts invalid columns if u dont mind plz can u explain your storedprocedure

Zoran Horvat · Answer

Then try like this: CREATE PROCEDURE InsertRow( @SendMessage VARCHAR(100) @Description VARCHAR(100) @Contacts INT) AS BEGIN DECLARE @lastID INT DECLARE @lastContacts INT SELECT @lastID=MAX(CustomerID) FROM Customer; SELECT @lastContacts=Contacts FROM Customer WHERE CustomerID=@lastID IF (@lastID IS NOT NULL AND @lastContacts UPDATE Customer SET Delta_contacts = @Contacts - Contacts WHERE CustomerID=@lastID; INSERT INTO Customer(SendMessage Description Contacts Delta_contacts) VALUES (@SendMessage @Description @Contacts 0) END This procedure will not update last record if its Contacts is not smaller than new value. However new record will be inserted every time. Zoran

karthik parcha · Answer

Hi SELECT 1 FROM Karthik WHERE empid = @empid in this query you write 1 from table name i didnt understand why you have write 1 here i want to update based on ID so plz can u clarify my doubt

karthik parcha · Answer

actually my table is if recent value lessthan present value then value should be displayed in anothercolumn and i have to and update the previous record's DELTA_CONTACTS' column with that value. CustometIDSendmessageDescriptionCOntactsDelta_contacts(delta means difference) 1 karthik Hyd 20 0 2 parcha hyd 10 10 and i have to and update the previous record's DELTA_CONTACTS' column with that value.

John Penn · Answer

Sorry that should have been subtractions . ;) To call the proc: exec InsertSalary 1 'k' 300 results: Empid name sal expsal 1 k 300.00 0.00 calling it again with a new salary: exec InsertSalary 1 'k' 500 results: Empid name sal expsal 1 k 300.00 200.00

karthik parcha · Answer

Hi Thanq i will try it if their any doubts i will contact you Thanks karthik

karthik parcha · Answer

Hi Thanq i will try it if their any doubtsi will contact you Thanks karthik

Zoran Horvat · Answer

Here is the CREATE PROCEDURE script which creates procedure InsertRow which should solve your problem: CREATE PROCEDURE InsertRow( @SendMessage VARCHAR(100) @Description VARCHAR(100) @Contacts INT) AS BEGIN DECLARE @lastID INT SELECT @lastID=MAX(CustomerID) FROM Customer; IF (@lastID IS NOT NULL) UPDATE Customer SET Delta_contacts = @Contacts - Contacts WHERE CustomerID=@lastID; INSERT INTO Customer(SendMessage Description Contacts Delta_contacts) VALUES (@SendMessage @Description @Contacts 0) END In this script change VARCHAR(100) declarations with appropriate declarations from your database and change table name from Customer to whatever it is in your database. Zoran

John Penn · Answer

Given this table definition: CREATE TABLE [dbo].[Karthik]( [Empid] [int] NOT NULL [name] [varchar](50) NOT NULL [sal] [money] NOT NULL [expsal] [money] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Karthik] ADD CONSTRAINT [DF_Karthik_expsal] DEFAULT ((0.0)) FOR [expsal] GO This proc will handle the substractions: ALTER PROCEDURE InsertSalary -- Add the parameters for the stored procedure here @empid int @name varchar(50) @newsal money AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT 1 FROM Karthik WHERE empid = @empid IF (@@ROWCOUNT = 1) BEGIN -- record already exists for this employee so update it UPDATE Karthik SET expsal = @newsal - sal WHERE Empid = @empid END ELSE BEGIN -- this is a new employee record so insert it INSERT INTO Karthik (Empid name sal) VALUES (@empid @name @newsal) -- default value is already set for expsal no need to insert it here END END GO

karthik parcha · Answer

hi Zoran CustometIDSendmessageDescriptionCOntactsDelta_contacts(delta means difference) this is my actual table i just take the example of empployee My database is Sqlserver Thanks Karthik

Zoran Horvat · Answer

Which database are you using (Oracle MS SQL MySql)? Are these column names correct: EmpIDEMpnameSalExpectedsal. Zoran

karthik parcha · Answer

hmm actually my table is if recent value lessthan present value then value should be displayed in anothercolumn and i have to and update the previous record's DELTA_CONTACTS' column with that value. CustometIDSendmessageDescriptionCOntactsDelta_contacts(delta means difference) 1 karthik Hyd 20 0 2 parcha hyd 10 10 and i have to and update the previous record's DELTA_CONTACTS' column with that value. plz can u write query for this iam tryng 2hrs but iam to unable getting that query

Zoran Horvat · Answer

What would be exact rule for subtraction - how would your example continue if you insert rows 3 4 5... Zoran