{"id":467,"date":"2015-12-27T20:57:51","date_gmt":"2015-12-28T02:57:51","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=467"},"modified":"2015-12-27T22:18:55","modified_gmt":"2015-12-28T04:18:55","slug":"t-sql-select-case-example","status":"publish","type":"post","link":"https:\/\/practical-prints.com\/?p=467","title":{"rendered":"T-SQL: Select Case Example"},"content":{"rendered":"

ARTICLE FROM ORIGINAL SITE – Young and Dumb!<\/strong><\/p>\n

Select Case allows you to present data in various way. You can take numbers and return text values and express
\ncertian conditions based on the value of a certain column.<\/p>\n

Another example would be that you have a table Naughty_Nice in the Santa Database a data column Nice (INT Datatype) .
\nThere are two distinct values in the column. The Value of 0 equals ‘No’ and the value 1 equals yes. You wanted to speed
\nstuff up and use less space by selecting a number and not use text Naughty\/Nice.<\/p>\n

Santa needs a list and he complains that 1 and 0 will not make sense to him at 30,000 feet. It’s so hard to
\ncommunicate to a guy that still thinks wooden toys are a big hit. He’s a bit too hands-on, and lacks the technical
\nskills to really understand that we want to optimize the database.
\n<\/p>\n

You want to query the table to find out which Child is Naughty and which is nice, but return a value that ‘Ole Red’ will
\nbenefit from. So you quickly point out that a New Yankee Workshop Maraton is on and Norm is demonstrating some $200,000
\ncombination tablesaw, sander with an auto-jig thingy. He takes off and you write the following query.<\/p>\n

\r\nSelect c.ChildName,\r\ng.Gift,\r\nl.Location,\r\ncase(n.Nice)\r\nwhen 1 then 'Nice'\r\nElse\r\n'Naughty'\r\nEnd\r\nFrom\r\nChild c, Gift G, Location L, Naughty_Nice n\r\nWhere\r\nc.childid=g.childid\r\nand\r\nc.childid=n.childid\r\nOrder by\r\nN.Nice N.nice, l.location,g.Gift, c.ChildName\r\n<\/pre>\n
You followed Normalization Rules that would join the tables Child, Gift, Location and Naughty_Nice by Childid.
\nYou are Sooooo Good!<\/p>\n
Below is an example that is a bit more complex, it takes a peek at the Orders table in the MS Sql Server NorthWind
\nDatabase. Drop off in June 1998? Hummh? I guess Pubs sold out to a major online bookseller or was pushed out of
\nbusiness by slow sales.<\/p>\n
—This example uses the SQL Server Example Database NorthWind
\n—Purpose: Define by Month and Year Total Orders for Years 1996-1998
\n—Use Case Function to gather Totals by Year
\n—Example By Jack Donnell jack@jackdonnell.com<\/p>\n
USE NorthWind\r\nGO\r\n\r\nSELECT\r\n\r\nMONTH(OrderDate) AS Month,\r\nSUM(CASE YEAR(OrderDate)\r\nWHEN 1996 THEN 1\r\nELSE 0\r\nEND) AS Orders1996,\r\nSUM(CASE YEAR(OrderDate)\r\nWHEN 1997 THEN 1\r\nELSE 0\r\nEND) AS Orders1997,\r\nSUM(CASE YEAR(OrderDate)\r\nWHEN 1998 THEN 1\r\nELSE 0\r\nEND) AS Orders1998\r\nFROM Orders\r\nGROUP BY MONTH(OrderDate)\r\nORDER BY MONTH(OrderDate)\r\nCOMPUTE SUM(SUM(CASE YEAR(OrderDate)\r\nWHEN 1996 THEN 1\r\nELSE 0\r\nEND) ),\r\nSUM(SUM(CASE YEAR(OrderDate)\r\nWHEN 1997 THEN 1\r\nELSE 0\r\nEND)),\r\nsum(SUM(CASE YEAR(OrderDate)\r\nWHEN 1998 THEN 1\r\nELSE 0\r\nEND))\r\n<\/pre>\n
The OutPut Would be :<\/p>\n
Month Orders1996 Orders1997 Orders1998
\n———– ———– ———– ———–
\n1 0 33 55
\n2 0 29 54
\n3 0 30 73
\n4 0 31 74
\n5 0 32 14
\n6 0 30 0
\n7 22 33 0
\n8 25 33 0
\n9 23 37 0
\n10 26 38 0
\n11 25 34 0
\n12 31 48 0<\/p>\n
sum
\n===========
\n152<\/p>\n
sum
\n===========
\n408<\/p>\n
sum
\n===========
\n270<\/p>\n
(13 row(s) affected)<\/p>\n","protected":false},"excerpt":{"rendered":"
ARTICLE FROM ORIGINAL SITE – Young and Dumb! Select Case allows you to present data in various way. You can take numbers and return text values and express certian conditions based on the value of a certain column. Another example …<\/p>\n
Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_seo_schema_type":"","_wpcom_ai_launchpad_first_post":false,"footnotes":""},"categories":[266,22,5],"tags":[303,249,36,295,52,291],"class_list":["post-467","post","type-post","status-publish","format-standard","hentry","category-dba","category-sql-server","category-t-sql","tag-dba","tag-script","tag-sql","tag-sql-server","tag-ssms","tag-t-sql"],"jetpack_shortlink":"https:\/\/wp.me\/phfTTO-7x","jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts\/467","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=467"}],"version-history":[{"count":9,"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts\/467\/revisions"}],"predecessor-version":[{"id":500,"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts\/467\/revisions\/500"}],"wp:attachment":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=467"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=467"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=467"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}