{"id":2689,"date":"2022-12-28T15:28:38","date_gmt":"2022-12-28T15:28:38","guid":{"rendered":"https:\/\/manualsqlserver.com\/?p=2689"},"modified":"2022-12-28T15:33:49","modified_gmt":"2022-12-28T15:33:49","slug":"usando-case-en-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=2689","title":{"rendered":"Usando Case en SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos__D.png\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"260\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos__D.png\" alt=\"\" class=\"wp-image-2690\"\/><\/a><\/figure>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Estructura Case<\/strong><\/p>\n\n\n\n<p>La estructura Case permite evaluar una o mas condiciones y seg\u00fan el resultado de esta, obtener un resultado diferente. Use Case cuando la cantidad de opciones puede hacer que escribir una estructura Condicional IF sea algo complicado.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p><strong>Para mas informaci\u00f3n vea:<\/strong><\/p>\n\n\n\n<p class=\"has-large-font-size\"><a href=\"https:\/\/manualsqlserver.com\/?p=1482\" target=\"_blank\" rel=\"noreferrer noopener\">Estructura Case SQL Server<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=211\" target=\"_blank\" rel=\"noreferrer noopener\">Ordenamientos en SQL Server<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=215\" target=\"_blank\" rel=\"noreferrer noopener\">Usando Joins en SQL Server<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=300\" target=\"_blank\" rel=\"noreferrer noopener\">Actualizaci\u00f3n de datos &#8211; Update<\/a><\/p>\n\n\n\n<p><strong>Usando la base de datos Northwind<\/strong><br>use Northwind<br>go<\/p>\n\n\n\n<p><strong>Establecer el formato de fechas en D\u00eda\/Mes\/A\u00f1o<\/strong><br>set dateformat dmy<br>go<\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script<\/script>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>Ordenar las \u00f3rdenes de Enero de 1997 de acuerdo a los d\u00edas de la semana<\/strong><\/p>\n\n\n\n<p>select O.OrderID As 'N\u00ba Orden', C.CompanyName As 'Cliente',<br>DATENAME(DW,O.OrderDate) As 'D\u00eda'<br>from Orders As O<br>join Customers As C on O.CustomerID = C.CustomerID<br>where O.OrderDate between '01\/01\/1997' and '31\/01\/1997'<br>Order by<br>Case DATENAME(DW,O.OrderDate)<br>When 'Lunes' then 1<br>When 'Martes' then 2<br>When 'Mi\u00e9rcoles' then 3<br>When 'Jueves' then 4<br>When 'Viernes' then 5<br>When 'S\u00e1bado' then 6<br>Else 7<br>End asc,<br>Cliente<br>go<br>La imagen muestra el resultado<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_01.png\"><img loading=\"lazy\" decoding=\"async\" width=\"444\" height=\"786\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_01.png\" alt=\"\" class=\"wp-image-2691\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_01.png 444w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_01-169x300.png 169w\" sizes=\"auto, (max-width: 444px) 100vw, 444px\" \/><\/a><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p>Las \u00f3rdenes de atendidas en agosto de 1997, ordenadas por empresa de env\u00edo.<br>Las empresas de env\u00edo son los registros de Shippers<br>select * from Shippers<br>go<br>La imagen muestra el resultado<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_02.png\"><img loading=\"lazy\" decoding=\"async\" width=\"433\" height=\"179\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_02.png\" alt=\"\" class=\"wp-image-2692\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_02.png 433w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_02-300x124.png 300w\" sizes=\"auto, (max-width: 433px) 100vw, 433px\" \/><\/a><\/figure>\n\n\n\n<p>Solamente son: Speedy Express, United Package y Federal Shipping<\/p>\n\n\n\n<p>select O.OrderID As 'N\u00ba Orden', C.CompanyName As 'Cliente',<br>S.CompanyName As 'Courier'<br>from Orders As O<br>join Customers As C on O.CustomerID = C.CustomerID<br>join Shippers As S on O.ShipVia = S.ShipperID<br>where O.ShippedDate between '01\/08\/1997' and '31\/08\/1997'<br>Order by<br>Case S.CompanyName<br>When 'Federal Shipping' then 1<br>When 'Speedy Express' then 2<br>When 'United Package' then 3<br>End,<br>Cliente<br>go<br>La imagen muestra el resultado<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_03.png\"><img loading=\"lazy\" decoding=\"async\" width=\"501\" height=\"784\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_03.png\" alt=\"\" class=\"wp-image-2693\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_03.png 501w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_03-192x300.png 192w\" sizes=\"auto, (max-width: 501px) 100vw, 501px\" \/><\/a><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script<\/script>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Ejercicio 3<\/strong><\/p>\n\n\n\n<p class=\"has-large-font-size\"><strong>Usando Case al hacer una actualizaci\u00f3n<\/strong><\/p>\n\n\n\n<p>Actualizar los precios de los productos, si la cantidad en Stock es mayor o igual a<br>100 aumentar en 10%, si es entre 50 y 99 aumentar 15%, si es entre 1 y 49 aumentar<br>20%, si no existe stock no debe variar el precio.<\/p>\n\n\n\n<p>Para ver los productos y sus precios actuales.<br>select P.ProductID As 'C\u00f3digo', P.ProductName As 'Descripci\u00f3n',<br>P.UnitPrice as 'Precio', P.UnitsInStock As 'Stock'<br>from Products As P order by P.UnitsInStock desc<br>go<br>La imagen muestra el resultado<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_04.png\"><img loading=\"lazy\" decoding=\"async\" width=\"523\" height=\"775\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_04.png\" alt=\"\" class=\"wp-image-2694\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_04.png 523w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_04-202x300.png 202w\" sizes=\"auto, (max-width: 523px) 100vw, 523px\" \/><\/a><\/figure>\n\n\n\n<p><strong>Para hacer la actualizaci\u00f3n de acuerdo al enunciado<\/strong><br>UPDATE Products<br>SET UnitPrice = UnitPrice *<br>CASE<br>WHEN UnitsInStock &gt;= 100 THEN 1.10<br>WHEN UnitsInStock &gt;= 50 and UnitsInStock &lt;= 99 THEN 1.15 WHEN UnitsInStock &gt;= 1 and UnitsInStock &lt;= 49 THEN 1.20<br>ELSE 0.00<br>END<br>go<\/p>\n\n\n\n<p>Volvemos a listar para ver los nuevos precios<br>select P.ProductID As 'C\u00f3digo', P.ProductName As 'Descripci\u00f3n',<br>P.UnitPrice as 'Precio', P.UnitsInStock As 'Stock'<br>from Products As P order by P.UnitsInStock desc<br>go<br>La imagen muestra el resultado<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_05.png\"><img loading=\"lazy\" decoding=\"async\" width=\"526\" height=\"779\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_05.png\" alt=\"\" class=\"wp-image-2695\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_05.png 526w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_05-203x300.png 203w\" sizes=\"auto, (max-width: 526px) 100vw, 526px\" \/><\/a><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Ejercicio 4<\/strong><\/p>\n\n\n\n<p class=\"has-large-font-size\"><strong>Case y valores Null<\/strong><\/p>\n\n\n\n<p>Listar los Clientes y ordenarlos por Regi\u00f3n, primero los que tienen asignada una<br>y luego ordenarlos por pais. En lugar de mostrar el valor de Null mostrar \"No especificada\".<\/p>\n\n\n\n<p>select C.CustomerID As 'C\u00f3digo', C.CompanyName As 'Cliente',<br>C.Country As 'Pa\u00eds',<br>Case<br>When C.Region IS NULL Then 'No especificada'<br>Else C.Region<br>End As 'Regi\u00f3n'<br>from Customers As C<br>order by<br>Case<br>When C.Region IS NULL Then 1<br>Else 0<br>End,<br>C.Country<br>go<\/p>\n\n\n\n<p>La imagen muestra el resultado<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_06.png\"><img loading=\"lazy\" decoding=\"async\" width=\"727\" height=\"781\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_06.png\" alt=\"\" class=\"wp-image-2696\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_06.png 727w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/12\/SQLServer_EstructuraCase_ConOrdenamientos_06-279x300.png 279w\" sizes=\"auto, (max-width: 727px) 100vw, 727px\" \/><\/a><\/figure>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script<\/script>\n","protected":false},"excerpt":{"rendered":"<p>Estructura Case La estructura Case permite evaluar una o mas condiciones y seg\u00fan el resultado de esta, obtener un resultado diferente. Use Case cuando la cantidad de opciones puede hacer que escribir una estructura Condicional IF sea algo complicado.<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=2689\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":2690,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,8],"tags":[154,43,51],"class_list":["post-2689","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","category-programacion","tag-case","tag-order-by","tag-select","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2689","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=2689"}],"version-history":[{"count":2,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2689\/revisions"}],"predecessor-version":[{"id":2701,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2689\/revisions\/2701"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/2690"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2689"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2689"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2689"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}