{"id":5573,"date":"2013-05-06T00:00:01","date_gmt":"2013-05-06T00:00:01","guid":{"rendered":"http:\/\/craftydba.com\/?p=5573"},"modified":"2016-04-23T01:05:29","modified_gmt":"2016-04-23T01:05:29","slug":"compound-operators","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=5573","title":{"rendered":"Compound Operators"},"content":{"rendered":"<p>I am going to forge ahead with my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions. <\/p>\n<p>I will exploring the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/cc645922.aspx\">Compound Operators<\/a> today.  These operators are a short hand for taking a variable @V, applying some operator O and storing the result as variable @V.  A long way to write out adding 2 to variable @V is the expression @V = @V + 2 while the short way is the expression @V += 2.<\/p>\n<p>T-SQL supports the following five compound math operations:  <a href=\"http:\/\/en.wikipedia.org\/wiki\/Addition\">ADDITION<\/a>, <a href=\"http:\/\/en.wikipedia.org\/wiki\/Subtraction\">SUBTRACTION<\/a>, <a href=\"http:\/\/en.wikipedia.org\/wiki\/Multiplication\">MULTIPLICATION<\/a>, <a href=\"http:\/\/en.wikipedia.org\/wiki\/Division\">DIVISION<\/a>, and <a href=\"http:\/\/en.wikipedia.org\/wiki\/Modulo_operation\">MODULUS<\/a>.  <\/p>\n<p>The examples below performs a simple calculation using each operator.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"general math - compound operators\">\r\n--\r\n--  Compound operators - Math (+-*\/%)\r\n--\r\n\r\ndeclare @x tinyint = 0;\r\n\r\n-- Addition\r\nselect @x += 4;\r\nselect @x as comp_addition\r\n\r\n-- Subtraction\r\nselect @x -= 1;\r\nselect @x as comp_subtraction\r\n\r\n-- Multiplication\r\nselect @x *= 4;\r\nselect @x as comp_multiplication\r\n\r\n-- Division\r\nselect @x \/= 2;\r\nselect @x as comp_division\r\n\r\n-- Modulo\r\nselect @x %= 5;\r\nselect @x as comp_modulo\r\n<\/pre>\n<\/p>\n<p>The output of each calculation is listed below.<\/p>\n<pre class=\"lang:TSQL theme:epicgeeks\" title=\"output\">\r\noutput: \r\n\r\ncomp_addition\r\n-------------\r\n4\r\n\r\ncomp_subtraction\r\n----------------\r\n3\r\n\r\ncomp_multiplication\r\n-------------------\r\n12\r\n\r\ncomp_division\r\n-------------\r\n6\r\n\r\ncomp_modulo\r\n-----------\r\n1\r\n<\/pre>\n<\/p>\n<p>In addition to math, T-SQL supports the following three compound bitwise operations:  <a href=\"https:\/\/en.wikipedia.org\/wiki\/Bitwise_operation#AND\">AND<\/a>, <a href=\"https:\/\/en.wikipedia.org\/wiki\/Bitwise_operation#OR\">OR<\/a> and <a href=\"https:\/\/en.wikipedia.org\/wiki\/Bitwise_operation#XOR\">XOR<\/a>.  <\/p>\n<p>The examples below performs a simple calculation using each operator.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"bitwise math - compound operators\">\r\n--\r\n--  Compound operators - Bitwise (+-*\/%)\r\n--\r\n\r\n-- Operator & (AND)\r\ndeclare @a tinyint = 0xFF;\r\nset @a &= 0x02;\r\nprint 'low_nibble_bit_two = 0x' + format(@a, 'X2');\r\nprint ' '; \r\n\r\n-- Operator | (OR)\r\ndeclare @o tinyint = 0x00;\r\nset @o |= 0x40;\r\nprint 'high_nibble_bit_three = 0x' + format(@o, 'X2');\r\nprint ' '; \r\n\r\n-- Operator | (XOR)\r\ndeclare @x tinyint = 0xAA;\r\nset @x ^= 0x55;\r\nprint 'all bits are on = 0x' + format(@x, 'X2');\r\nprint ' '; \r\n\r\n<\/pre>\n<\/p>\n<p>The output of each calculation is listed below.<\/p>\n<pre class=\"lang:TSQL theme:epicgeeks\" title=\"output\">\r\noutput: \r\n\r\nlow_nibble_bit_two = 0x02\r\n \r\nhigh_nibble_bit_three = 0x40\r\n \r\nall bits are on = 0xFF\r\n<\/pre>\n<\/p>\n<p>Please note that I have used both the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms187330.aspx\">SELECT<\/a> and <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms189484.aspx\">SET<\/a> statements to perform assignments using compound operators.  This means that a variable can be used to get a summation from a query.<\/p>\n<p>For instance, let&#8217;s believe the chief financial officer of <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/aa992075.aspx\">Adventure Works<\/a> wanted us to get the hourly run rate for the company.  We want to only consider active employees, people who do not have a termination date.  The solution below uses a common table expression to find active employees.  <\/p>\n<p>The @total variable is used with the addition compound operator to calculate a total hourly cost.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"real world example - compound operators\">\r\n--\r\n--  Calculate hourly run rate\r\n--\r\n\r\nuse [AdventureWorks2012];\r\ndeclare @total as money = 0;\r\n;\r\nwith cte_Active_Employees\r\nas\r\n(\r\nselect distinct\r\n BusinessEntityID\r\nfrom \r\n  [HumanResources].[EmployeeDepartmentHistory]\r\nwhere \r\n  EndDate is null\r\n)\r\nselect \r\n  @total += Rate \r\nfrom \r\n  [HumanResources].[EmployeePayHistory] as h \r\ninner join \r\n  cte_Active_Employees as a\r\non \r\n  h.BusinessEntityID = a.BusinessEntityID\r\nprint 'Daily run rate is ' + format(@total, 'C') + ' per hour.';\r\n<\/pre>\n<\/p>\n<pre class=\"lang:TSQL theme:epicgeeks\" title=\"output\">\r\noutput: \r\n    Daily run rate is $5,611.78 per hour.\r\n<\/pre>\n<\/p>\n<p>Next time, I will be exploring the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms189773.aspx\">Logical Operators<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I am going to forge ahead with my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions. I will exploring the Compound Operators today. These operators are a short hand for taking a variable @V, applying some operator O and storing the result as variable @V. A long way to write out adding 2 to variable @V is the expression @V = @V + 2 while the short way is the expression&hellip;<\/p>\n","protected":false},"author":1,"featured_media":5430,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[814],"tags":[842,31,15,28,29],"class_list":["post-5573","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-very-short-articles","tag-compound-operator","tag-database-developer","tag-john-f-miner-iii","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/5573","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5573"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/5573\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/5430"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5573"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5573"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5573"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}