?
Solved

sql server 2008 query

Posted on 2013-10-28
6
Medium Priority
?
307 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
6 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 400 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 400 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Assisted Solution

by:svalekar
svalekar earned 400 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 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 400 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 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