Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Order of Precedence in T-SQL

Posted on 2010-09-21
7
629 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
ID: 33729365
This might help you understand the precedence order

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

Expert Comment

by:cyberkiwi
ID: 33729372
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
ID: 33729399
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 40

Expert Comment

by:Sharath
ID: 33729794

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
ID: 33745074
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
ID: 33750149
Thanks for the useful reference
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33750478
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

829 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