{"id":198,"date":"2017-05-15T21:46:47","date_gmt":"2017-05-15T21:46:47","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=198"},"modified":"2020-05-21T00:19:13","modified_gmt":"2020-05-21T00:19:13","slug":"consultas-con-campos-calculados","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=198","title":{"rendered":"Campos calculados en Select de SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"300\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ConsultasCamposCalculadosD.png\" alt=\"\" class=\"wp-image-1411\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ConsultasCamposCalculadosD.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ConsultasCamposCalculadosD-150x150.png 150w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Campos calculados en Consultas<\/strong><\/h1>\n\n\n\n<p>Los campos calculados en una consulta son campos que se construyen en base a los existentes en la tabla.<\/p>\n\n\n\n<p>Los campos calculados siguen cualquiera de los formatos siguientes:<\/p>\n\n\n\n<p><strong>NombreCampoCalculado = Expresi\u00f3n que lo calcula<\/strong><br><strong>o<\/strong><br><strong>Expresi\u00f3n que lo calcula As &#8216;Nombre de campo calculado&#8217;<\/strong><\/p>\n\n\n\n<p>Si el nombre del campo calculado incluye espacios se debe encerrar entre corchetes.<\/p>\n\n\n\n<!--more-->\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<h3 class=\"wp-block-heading\">Ejercicios<\/h3>\n\n\n\n<p>Usando Northwind<br>use Northwind<br>go<\/p>\n\n\n\n<p><strong>1. El c\u00f3digo del empleado y su nombre completo (el nombre completo no existe en la tabla)<\/strong><\/p>\n\n\n\n<p>select E.EmployeeID As &#8216;C\u00f3digo&#8217;,<br>Empleado = E.LastName + Space(1) + E.FirstName<br>from Employees As E<br>go<\/p>\n\n\n\n<p>&#8212; Otra forma<br>select E.EmployeeID As &#8216;C\u00f3digo&#8217;,<br>E.LastName + Space(1) + E.FirstName As &#8216;Empleado&#8217;<br>from Employees As E<br>go<\/p>\n\n\n\n<p><strong>2. Empleado y su edad (en la tabla hay fecha de nacimiento)<\/strong><\/p>\n\n\n\n<p>select E.EmployeeID As &#8216;C\u00f3digo&#8217;,<br>E.LastName + Space(1) + E.FirstName As &#8216;Empleado&#8217;,<br>Edad = Datediff(yy,E.BirthDate,GetDate())<br>from Employees As E<br>go<br>En la instrucci\u00f3n anterior se muestran los dos formatos de como presentar campos calculados.<\/p>\n\n\n\n<p><strong>3. Los productos y el valor del Stock (Precio * Stock)<\/strong><\/p>\n\n\n\n<p>select P.ProductName As &#8216;Producto&#8217;, P.ProductID As &#8216;C\u00f3digo&#8217;,<br>[Valor del Stock] = P.UnitPrice * P.UnitsInStock,<br>P.UnitPrice As &#8216;Precio&#8217;, P.UnitsInStock As &#8216;Stock&#8217;<br>from Products As P<br>go<\/p>\n\n\n\n<p><strong>4. Productos y las unidades de compra urgente.&nbsp;<\/strong><strong>Existen unidades para comprar urgente cuando el stock es menor a las unidades en orden<\/strong><\/p>\n\n\n\n<p>select P.ProductID As &#8216;C\u00f3digo&#8217;,P.ProductName As &#8216;Producto&#8217;,<br>P.UnitsInStock As &#8216;Stock&#8217;, P.UnitsOnOrder As &#8216;En Orden&#8217;,<br>[Por comprar] = iif(P.UnitsOnOrder &gt; P.UnitsInStock,P.UnitsOnOrder &#8211; P.UnitsInStock,0)<br>from Products As P<br>go<\/p>\n\n\n\n<p><strong>5. Las \u00f3rdenes, el monto total de la misma y el impuesto, suponiedo que se trate de un 12%.<\/strong><\/p>\n\n\n\n<p>select O.OrderID As &#8216;N\u00ba Orden&#8217;,<br>Format(O.OrderDate,&#8217;dd\/MM\/yy&#8217;) As &#8216;Fecha de Orden&#8217;,<br>O.Freight as &#8216;Monto&#8217;, Impuesto = O.Freight * 0.12<br>from Orders As O<br>go<\/p>\n\n\n\n<p><strong>Notas<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Puede incluirse la cantidad de campos calculados que se necesite, estos siempre se calculan en base a los campos existentes en la tabla.<\/li><li>Es recomendable para optimizar el trabajo de las consultas, incluir en las tablas los campos calculados que se usan con frecuencia.<\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Campos calculados en Consultas Los campos calculados en una consulta son campos que se construyen en base a los existentes en la tabla. Los campos calculados siguen cualquiera de los formatos siguientes: NombreCampoCalculado = Expresi\u00f3n que lo calculaoExpresi\u00f3n que lo calcula As &#8216;Nombre de campo calculado&#8217; Si el nombre del campo calculado incluye espacios se &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=198\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1411,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[128,51],"class_list":["post-198","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","tag-campos-calculados","tag-select","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/198","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=198"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/198\/revisions"}],"predecessor-version":[{"id":1413,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/198\/revisions\/1413"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1411"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=198"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=198"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=198"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}