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 |
|
nt86
Yak Posting Veteran
54 Posts |
 Posted - 2009-12-03 : 12:11:45
|
| Im using a temp table in my stored procedure, i have all my fields populating but i now want to calculate savings for them so i need to multiple the records in the fields. When i try and multiple out the 1st part of my code the (hours_off_day * day_hour_rate), it works fine but when i add the 2nd part of my code (hours_off_night * night_hour_rate) it doesn't return anything into the total_savings column, should i be using SUM as well?.The fields in my table are defined belowhours_off_day int,hours_off_night int,day_hour_rate float,night_hour_rate float,total_savings float,UPDATE #savingstemp SET total_savings = (hours_off_day * day_hour_rate) + (hours_off_night * night_hour_rate)Any ideas why this is? Thanksniall |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-12-03 : 12:19:21
|
| Check this it works fine..select (10*2.0000)+(40*3.0000)It seams to be the problem with your data!Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-12-03 : 12:51:21
|
| you probably have a NULL value in night_hour_rate |
|
|
|
nt86
Yak Posting Veteran
54 Posts |
Posted - 2009-12-03 : 17:06:15
|
quote:
Originally posted by russell
you probably have a NULL value in night_hour_rate
Thanks for the post, i dont have a NULL value in night_hour_rate but i may have a NULL value in hours_off_day or hours_off_night. When i say 'may' i mean it is possible and that is the problem i imagine at the moment. Any ideas how I would work around this? thanksniall |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-12-03 : 17:12:15
|
| SET total_savings = (isnull(hours_off_day, 0) * day_hour_rate) + (isnull(hours_off_night, 0) * night_hour_rate) |
|
|
|
nt86
Yak Posting Veteran
54 Posts |
Posted - 2009-12-04 : 08:47:20
|
That worked thanks for that :)quote:
Originally posted by russell
SET total_savings = (isnull(hours_off_day, 0) * day_hour_rate) + (isnull(hours_off_night, 0) * night_hour_rate)
niall |
|
|
|
|
|
|
|
|
Multiplication & Addition