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 |
|
jrockfl
Posting Yak Master
223 Posts |
 Posted - 2005-01-07 : 09:46:08
|
| Here is a snip from my SQL statement, sometimes GoalPerRep returns as 0. Is it possible to default it to 1600?SELECT GoalAmount * 2 As ProdGoalAmount, (Salesgoal / TotalTeamGoal) * (GoalAmount * 2) As GoalPerRepFROM table |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-01-07 : 09:47:33
|
case when (Salesgoal / TotalTeamGoal) * (GoalAmount * 2) = 0 then 1600 else (Salesgoal / TotalTeamGoal) * (GoalAmount * 2) endGo with the flow & have fun! Else fight the flow  |
 |
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2005-01-07 : 10:27:07
|
| I'm still returning 0, instead of 1600. does this look right?SELECT slspsn_no, slspsn_name, SUM(SalesAmount) As MonthlySales, SalesGoal As MonthlySalesGoal, TotalTeamGoal, GoalAmount * 2 As ProdGoalAmount, case when (Salesgoal / TotalTeamGoal) * (GoalAmount * 2) = 0 then 1600 else (Salesgoal / TotalTeamGoal) * (GoalAmount * 2) end As GoalPerRep |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-01-07 : 10:45:58
|
| could it be you have a null in there somewhere?because that looks ok to me...Go with the flow & have fun! Else fight the flow |
|
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2005-01-07 : 11:36:10
|
| You were right, there was a null in one of my sub queries. So I ended up with this to get things working right. Thanks for your help.SELECT Case when IsNull(SUM(Sls_Amt) / 6, 0) = 0 Then 10000 Else SUM(Sls_Amt) / 6 End As GoalAmount |
|
|
|
|
|
|
SQL Case Help?