Solved

sql server 2008 query

Posted on 2013-10-28
6
293 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
Comment Utility
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
Comment Utility
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
Comment Utility
or, staying purely within date functions :)

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


(never say never around here :)
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 2

Assisted Solution

by:svalekar
svalekar earned 100 total points
Comment Utility
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:ScottPletcher
ScottPletcher earned 100 total points
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SLQ View not updating 10 46
SQL Server Log File Space 6 33
t-sql money data type decimal places 4 25
SQL Script to find duplicates 16 18
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now