Solved

sql server 2008 query

Posted on 2013-10-28
6
301 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 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 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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…

733 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