Perform calculations - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Perform calculations

Author

Topic

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-10 : 08:32:41

Hello all.

I am looking to perform a calculation and enter the reult into a field within my table. The fields that I need to base the calculation on are all in one table (SALARY). The fields are: SALARY and BASIC_HOURS and the result is to be entered into field HOURLY_RATE. The actualy calculation to be preformed is:

hourly rate = ()salary / 52)/basic hours)

Thanks for reading :)

alanlambert
Starting Member

26 Posts

Posted - 2006-08-10 : 08:36:35

update salary
set hourly_rate = (salary / 52)/basic_hours

Alan

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-10 : 08:44:36

Hi Alan, thats great. Thanks very much.

Whilst researching this i'd looked into cursors. I've written the following cursor:

declare
CURSOR_SET_HOURLY_RATE CURSOR LOCAL
for
select
SALARY,
BASIC_HOURS
from
SALHISTY
where
SALHISTY_REF IS NOT NULL

open CURSOR_SET_HOURLY_RATE

declare
@SALARY MONEY,
@BASIC_HOURS INT,
@HOURLYRATE DECIMAL(9,2)

fetch next from CURSOR_SET_HOURLY_RATE into
@SALARY,
@BASIC_HOURS

while (@@FETCH_STATUS<>-1)
begin

@HOURLYRATE = ((@SALARY/52)/@BASIC_HOURS) --CALCULATE HOURLY RATE

UPDATE SALHISTY
SET HOURLYRATE = @HOURLYRATE

fetch next from CURSOR_SET_HOURLY_RATE into
@SALARY,
@BASIC_HOURS

end

close CURSOR_SET_HOURLY_RATE
deallocate CURSOR_SET_HOURLY_RATE

But i keep getting the following errors:

Line 27: Incorrect syntax near '@HOURLYRATE'.

Incorrect syntax near the keyword 'end'.

Can anyone shed any light on this for me.

Thanks again people :)

alanlambert
Starting Member

26 Posts

Posted - 2006-08-10 : 08:50:46

The problem is on the line

@HOURLYRATE = ((@SALARY/52)/@BASIC_HOURS) --CALCULATE HOURLY RATE

It should read

SET @HOURLYRATE = ((@SALARY/52)/@BASIC_HOURS) --CALCULATE HOURLY RATE

But use the update statement above rather than a cursor.

Alan

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-10 : 08:51:56

Thanks very much Alan. Greatly appreciated!

Gav

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources