I'm trying to get a weighted average using executesql and hitting a wall.
The query looks like this
Select Account, sum(case when Week=30 then sales else 0 end) / sum(case when week=30 then people else 0 end)
group by Account
If I change the "/" for a comma it returns each column with no problem. but when I try to divide the total of sales for a given week and the total of people for a given week to get the sales per person, I get nothing...
NOTE: I posted an abreviated version of the query, the query has several CASE functions for several weeks but the number of case statements isn't the issue at hand.
Any ideas? Work arounds?