In the code below, it appears that I have a different result whenever I change the order of the element above the denominator. Since these are just simple addition and subtraction operators why would it make a difference how they are ordered. I was finally able to get it woking properly but I am a bit confused about the operatior order of precedence.

(SELECT DV.ForecastYear, @Metric1, ( Sum(case when MetricName=@Metric1 then MetricValue end) + Sum(case when MetricName=@Metric3 then MetricValue end) + Sum(case when MetricName=@Metric4 then MetricValue end) - Sum(case when MetricName=@Metric2 then MetricValue end) + Sum(case when MetricName=@Metric5 then MetricValue end) - Sum(case when MetricName=@Metric6 then MetricValue end) ) / Sum(case when MetricName=@Metric7 then MetricValue end) CalcResults, @spid FROM Metrics M2 INNER JOIN DataVersion DV ON M2.DataVersionRowID = DV.DataVersionRowID WHERE DV.DataVersionID = @DVID AND MetricName in (@Metric1, @Metric2, @Metric3, @Metric4, @Metric5, @Metric6, @Metric7) Group by DV.ForecastYear)

Are you sure you have no other changes except the order?
There should be no difference whatsoever however you order these lines:

Sum(case when MetricName=@Metric1 then MetricValue end)
+ Sum(case when MetricName=@Metric3 then MetricValue end)
+ Sum(case when MetricName=@Metric4 then MetricValue end)
- Sum(case when MetricName=@Metric2 then MetricValue end)
+ Sum(case when MetricName=@Metric5 then MetricValue end)
- Sum(case when MetricName=@Metric6 then MetricValue end)

Just a thought, if @Metric1 through 6 are always DISTINCT, this should simplify it

Sum(case when MetricName in (@Metric1,@Metric3,@Metric4,@Metric5) then MetricValue end)
- Sum(case when MetricName in (@Metric2,@Metric6) then MetricValue end)

But re your question, the order should not have mattered as all if that is your full query.

0

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

I suggest you to do the aggregations first and then apply the calculations.

SELECT ForecastYear, Metric1, (sum_1 + sum_2 + sum_3 - sum_4 + sum_5 - sum_6) / sum_7, spid FROM ( SELECT DV.ForecastYear, @Metric1 Metric1, SUM(CASE WHEN MetricName = @Metric1 THEN MetricValue END) sum_1, SUM(CASE WHEN MetricName = @Metric3 THEN MetricValue END) sum_2, SUM(CASE WHEN MetricName = @Metric4 THEN MetricValue END) sum_3, SUM(CASE WHEN MetricName = @Metric2 THEN MetricValue END) sum_4, SUM(CASE WHEN MetricName = @Metric5 THEN MetricValue END) sum_5, SUM(CASE WHEN MetricName = @Metric6 THEN MetricValue END) sum_6, SUM(CASE WHEN MetricName = @Metric7 THEN MetricValue END) sum_7, @spid spid FROM Metrics M2 INNER JOIN DataVersion DV ON M2.DataVersionRowID = DV.DataVersionRowID WHERE DV.DataVersionID = @DVID AND MetricName IN (@Metric1,@Metric2,@Metric3,@Metric4, @Metric5,@Metric6,@Metric7) GROUP BY DV.ForecastYear) t1

It really shouldnt matter. With divides, you need to be careful of using integers rather than decimals, so sometimes do a * 1.0 to ensure it uses decimal arithmatic.

The only thing I can think of off the top of my head is maybe NULL values upsetting the aggregate functions.

Using your original, and by the way both cyberkiwi and sharath have good suggestions above...

( Sum(case when MetricName=@Metric1 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric3 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric4 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric2 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric5 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric6 then MetricValue else 0.0 end)
)

-- should be the same as

( Sum(case when MetricName=@Metric1 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric2 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric3 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric4 then MetricValue else 0.0 end)
+ Sum(case when MetricName=@Metric5 then MetricValue else 0.0 end)
- Sum(case when MetricName=@Metric6 then MetricValue else 0.0 end)
)

also your denominator should also be checked, not just for "else 0" but also that it is in fact not zero.

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Long way back, we had to take help from third party tools in order to encrypt and decrypt data. Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, â€¦

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator wantâ€¦

Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as formâ€¦