Solved

Order of Precedence in T-SQL

Posted on 2010-09-21
7
626 Views
Last Modified: 2012-05-10
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)

Open in new window

0
Comment
Question by:chtullu135
7 Comments
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
Comment Utility
This might help you understand the precedence order

http://msdn.microsoft.com/en-us/library/ms190276.aspx
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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)
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 40

Expert Comment

by:Sharath
Comment Utility

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

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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.
0
 

Author Closing Comment

by:chtullu135
Comment Utility
Thanks for the useful reference
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Interesting choice - does that help explain your question ? Or just a useful reference ?

Whilst a good link, think all it was saying is what the other experts actually said - it should not matter.

The only time it might matter is starting off with subtracting - you have to subtract from something (or could " + (something * -1) " )

check below for what I was saying about NULLs - two scenarios, one is NULL metricvalue and the other is missing metricnames.

There are settings, and if running in batch mode might be needed to avoid an abort.

But anyway, just wanted to make sure you knew the problems with handling NULL values.


if object_id('tempdb..#mydata_source') is not null drop table #mydata_source

create table #mydata_source (Forecastyear int, MetricName varchar(20), metricValue decimal (18,6))



insert #mydata_source values (2009,'M2',222)

insert #mydata_source values (2009,'M5',555)

insert #mydata_source values (2009,'M6',666)

insert #mydata_source values (2009,'M7',777)

insert #mydata_source values (2010,'M1',111.111)

insert #mydata_source values (2010,'M2',222.222)

insert #mydata_source values (2010,'M3',333.333)

insert #mydata_source values (2010,'M4',444.444)

insert #mydata_source values (2010,'M5',555.555)

insert #mydata_source values (2010,'M7',777.777)

--insert #mydata_source values (2010,'M6',666.66)

insert #mydata_source values (2010,'M6',NULL)



declare @metric1 varchar(20)

declare @metric2 varchar(20)

declare @metric3 varchar(20)

declare @metric4 varchar(20)

declare @metric5 varchar(20)

declare @metric6 varchar(20)

declare @metric7 varchar(20)



set @metric1 = 'M1'

set @metric2 = 'M2'

set @metric3 = 'M3'

set @metric4 = 'M4'

set @metric5 = 'M5'

set @metric6 = 'M6'

set @metric7 = 'M7'



select forecastyear,

	(  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)

	)  as old_way,

    (  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)

    )  as new_way,

    (  0

    -  Sum(case when MetricName=@Metric6 then MetricValue else 0.0 end)

    -  Sum(case when MetricName=@Metric2 then MetricValue else 0.0 end)

    +  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=@Metric5 then MetricValue else 0.0 end)

    )  as new_way_jumbled

from #mydata_source

group by forecastyear

Open in new window

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Query Assistance 4 29
Database Containment - Benefits 6 24
replication - alerts? 4 18
Sql query to Stored Procedure 6 12
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now