Solved

sql server 2008 query

Posted on 2013-10-28
6
302 Views
Last Modified: 2013-10-30
I have a table and a query that  looks like this.

http://sqlfiddle.com/#!3/157bb/1

If you notice in my query I used a CASE statement to create a new column called OrderMonth2 which holds the actual name of the month pertaining to the value stored in the column called OrderMonth.

The naming of the months goes according to this table:

my table
My query creates the OrderMonth2 column correctly. In my example I use a case statement.

Is there a better way to write this query?  
I just wrote this query in a rush, but figured there might be a better way to get the same result set.
0
Comment
Question by:maqskywalker
[X]
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
6 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 39607166
Up through 2008 R2, no there is not a better way, as SELECT CASE Month(someDate) WHEN 1 THEN '1 month name' {repeat 11 times} END is the best.

Introduced in 2012 was the CHOOSE function, which still requires spelling all the months out, but saves a lot of typing. ChooseI just published an article on SQL Server CASE Solutions if you'd like to read more.

Hat tip:  mark_wills
0
 
LVL 25

Accepted Solution

by:
chaau earned 100 total points
ID: 39607237
How about datename function?
Here is a small math trick to shift the numbers 12 - (18 - [OrderMonth]) % 12:
SELECT [OrderID]
      ,[OrderNumber]
      ,[OrderMonth]
      -- added column to show name of month
	  ,OrderMonth2 = Datename(month, '2013-'+str(12 - (18 - [OrderMonth]) % 12)+'-01')
FROM [TestOrders]

Open in new window

SQL Fiddle
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39607456
or, staying purely within date functions :)

, datename(month,dateadd(month,[OrderMonth],'19000601'))


(never say never around here :)
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 2

Assisted Solution

by:svalekar
svalekar earned 100 total points
ID: 39608435
SELECT OrderID ,
       OrderNumber ,
       OrderMonth ,
       DATENAME(MONTH , '2013' + '-' + CONVERT( varchar(2) , OrderMonth) + '-' + '01')AS OrderMonth2  FROM TestOrders;

OR

select DATENAME(month,29* OrderMonth),29* OrderMonth FROM TestOrders
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 39609302
I strongly suggest adding a computed column to the original table.  Then the OrderMonth2 value is only calculated in one place, and it can easily be used in all clauses of the SELECT: WHERE, ORDER BY, etc..


ALTER TABLE dbo.TestOrders
ADD OrderMonth2 AS
    --or use DATENAME() if you prefer
    CAST(SUBSTRING('July     August   SeptemberOctober  November December January  February March    April    May      June', OrderMonth * 9 - 8, 9) AS varchar(9))


You can then use OrderMonth2 just like any other column; for example:

SELECT OrderMonth, OrderMonth2, ...
FROM ...
WHERE
    OrderMonth2 IN ('January', 'February')
ORDER BY
    OrderMonth2 DESC
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 39610290
not sure why you would resort to string manipulation for a simple date calculation and would suggest use of date functions instead

either as already proposed in a query or in the computed column
e.g.

ALTER TABLE dbo.TestOrders
ADD OrderMonth2 AS (
  datename(month,dateadd(month,[OrderMonth],'19000601'))
                   )
;
    CREATE TABLE [dbo].[TestOrders](
    	[OrderID] [int] NULL,
    	[OrderNumber] [varchar](50) NULL,
    	[OrderMonth] [int] NULL
    ) ON [PRIMARY] 
    GO
    INSERT [dbo].[TestOrders] ([OrderID], [OrderNumber], [OrderMonth]) VALUES (1, N'1001', 1)
    INSERT [dbo].[TestOrders] ([OrderID], [OrderNumber], [OrderMonth]) VALUES (2, N'1002', 2)
    INSERT [dbo].[TestOrders] ([OrderID], [OrderNumber], [OrderMonth]) VALUES (3, N'1003', 3)
    INSERT [dbo].[TestOrders] ([OrderID], [OrderNumber], [OrderMonth]) VALUES (4, N'1004', 4)
    INSERT [dbo].[TestOrders] ([OrderID], [OrderNumber], [OrderMonth]) VALUES (5, N'1005', 5)
    INSERT [dbo].[TestOrders] ([OrderID], [OrderNumber], [OrderMonth]) VALUES (6, N'1006', 6)
    INSERT [dbo].[TestOrders] ([OrderID], [OrderNumber], [OrderMonth]) VALUES (7, N'1007', 7)
    INSERT [dbo].[TestOrders] ([OrderID], [OrderNumber], [OrderMonth]) VALUES (8, N'1008', 8)
    INSERT [dbo].[TestOrders] ([OrderID], [OrderNumber], [OrderMonth]) VALUES (9, N'1009', 9)
    INSERT [dbo].[TestOrders] ([OrderID], [OrderNumber], [OrderMonth]) VALUES (10, N'1010', 10)
    INSERT [dbo].[TestOrders] ([OrderID], [OrderNumber], [OrderMonth]) VALUES (11, N'1011', 11)
    INSERT [dbo].[TestOrders] ([OrderID], [OrderNumber], [OrderMonth]) VALUES (12, N'1012', 12)
    ;
    GO
    
    ALTER TABLE dbo.TestOrders
    ADD OrderMonth2 AS ( 
      datename(month,dateadd(month,[OrderMonth],'19000601'))
                       )
    ;
    GO

**Query 1**:

    SELECT [OrderID]
          ,[OrderNumber]
          ,[OrderMonth]
          -- added column to show name of month
    	  ,using_case = (CASE WHEN [OrderMonth] = 1 THEN 'July'
    			                WHEN [OrderMonth] = 2 THEN 'August'
    			                WHEN [OrderMonth] = 3 THEN 'September' 
    			                WHEN [OrderMonth] = 4 THEN 'October' 
    			                WHEN [OrderMonth] = 5 THEN 'November' 
    			                WHEN [OrderMonth] = 6 THEN 'December' 
    			                WHEN [OrderMonth] = 7 THEN 'January' 
    			                WHEN [OrderMonth] = 8 THEN 'February' 
    			                WHEN [OrderMonth] = 9 THEN 'March'   
    			                WHEN [OrderMonth] = 10 THEN 'April' 
    			                WHEN [OrderMonth] = 11 THEN 'May' 
    			                WHEN [OrderMonth] = 12 THEN 'June' 	
    			                END) 
          , OrderMonth2
      FROM [TestOrders]

**[Results][2]**:
    
    | ORDERID | ORDERNUMBER | ORDERMONTH | USING_CASE | ORDERMONTH2 |
    |---------|-------------|------------|------------|-------------|
    |       1 |        1001 |          1 |       July |        July |
    |       2 |        1002 |          2 |     August |      August |
    |       3 |        1003 |          3 |  September |   September |
    |       4 |        1004 |          4 |    October |     October |
    |       5 |        1005 |          5 |   November |    November |
    |       6 |        1006 |          6 |   December |    December |
    |       7 |        1007 |          7 |    January |     January |
    |       8 |        1008 |          8 |   February |    February |
    |       9 |        1009 |          9 |      March |       March |
    |      10 |        1010 |         10 |      April |       April |
    |      11 |        1011 |         11 |        May |         May |
    |      12 |        1012 |         12 |       June |        June |



  [1]: http://sqlfiddle.com/#!3/b9871/2

Open in new window

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

691 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