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.
| Author |
Topic |
|
roper
Starting Member
3 Posts |
 Posted - 2006-09-16 : 08:44:25
|
| Hi, I need help with calculated columns or something like that.If i need to make table of EMPLOYEE with types of salary and table of types indexes and their names. I Need in Employee table or another table make column that calculates his salary by the type, like regular employee have salary = hours * rateperhour , if another type i need calculate like salary = employeeid * hours and so on ... there is 5 types of salary.How i make salary calculation column in table of employees? |
|
|
roper
Starting Member
3 Posts |
Posted - 2006-09-16 : 12:07:07
|
| ok just answer me, is there good idea to calculate salary of every employee in VIEW ? not in TableLike:"SELECT Employee, dbo.GetSalary(EmployeeID) as SalaryFROM dbo.Employees"and user-defined function "GetSalary" calculates Salary for every Employee? |
 |
|
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2006-09-16 : 13:09:57
|
| I'm not sure exactly what you're asking here, but yes, I've just learned that you can use a function result as a column, and you can use CASE to use the right function. So you could use something like this:SELECT employeeID, Firstname, Lastname,CASE WHEN (EMPLOYEETYPE= 'Salaried')THEN fnSalaried(param1,param2,etc) ELSECASE WHEN (EMPLOYEETYPE= 'Hourly')THEN fnHourly(param1,param2,etc) ELSECASE WHEN (EMPLOYEETYPE= 'Commisioned')THEN fnComm(param1,param2,etc)AS BaseSalary,column3, column4, etc.FROM<EMPLOYEE TABLE>Naturally, the employyee table has to contain a column indicating which type each employee is.-----------------Stephen |
|
|
|
roper
Starting Member
3 Posts |
Posted - 2006-09-17 : 01:19:22
|
quote:
Originally posted by stephenbaer
I'm not sure exactly what you're asking here, but yes, I've just learned that you can use a function result as a column, and you can use CASE to use the right function. So you could use something like this:SELECT employeeID, Firstname, Lastname,CASE WHEN (EMPLOYEETYPE= 'Salaried')THEN fnSalaried(param1,param2,etc) ELSECASE WHEN (EMPLOYEETYPE= 'Hourly')THEN fnHourly(param1,param2,etc) ELSECASE WHEN (EMPLOYEETYPE= 'Commisioned')THEN fnComm(param1,param2,etc)AS BaseSalary,column3, column4, etc.FROM<EMPLOYEE TABLE>Naturally, the employyee table has to contain a column indicating which type each employee is.-----------------Stephen
Ok, but everytime i request this query (view) this make all calculations again and again ... is it good idea to make like this? is there option to make calculation only on data changes and adding of data ... |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-09-17 : 02:02:03
|
quote:
Ok, but everytime i request this query (view) this make all calculations again and again ... is it good idea to make like this?
It depends on the complexity of the calculations you are doing in computed column. If it is just a simple expression, it's better to go for it!! quote:
is there option to make calculation only on data changes and adding of data
No, since calculation formula is stored in the system table and not the actual result of formula, there is no such option !!Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2006-09-17 : 09:44:07
|
quote:
Ok, but everytime i request this query (view) this make all calculations again and again ... is it good idea to make like this? is there option to make calculation only on data changes and adding of data ...
Well, if you think about it a column that returns salary is like one that returns age. It's inherently dynamic. It changes everytime someone gets a performance review or a promotion, just as age changes every time someone has a birthday. This means that recalculating every time is a good idea.-----------------Stephen |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2006-09-17 : 11:04:28
|
quote:
is there option to make calculation only on data changes and adding of data
In 2005, you can specify PERSISTED in the definition of a computed column (as long as the expression is deterministic). Never tried it myself, so I don't know what the performance is like. |
|
|
|
|
|
|
|
|
Calculated Column