Solved

sql server 2008 query

Posted on 2013-10-28
6
300 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 65

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 24

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 48

Expert Comment

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

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


(never say never around here :)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 48

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 31
Help in Bulk Insert 9 38
divide by zero error 23 18
get_systemdrive info from tsql? 1 6
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

825 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