Solved

Case Statement

Posted on 2013-01-24
6
581 Views
Last Modified: 2015-09-22
Hello:

I have this Case Statement that based on a the month value assigns the year. However when I run the query this 'Y' OR 'Y+1' in the yr field instead of the year number.

SELECT SName, 
CASE
WHEN M = '1' THEN 'Y+1'
WHEN M = '2' THEN 'Y+1'
WHEN M = '3' THEN 'Y+1'
WHEN M = '4' THEN 'Y'
WHEN M = '5' THEN 'Y'
WHEN M = '6' THEN 'Y'
WHEN M = '7' THEN 'Y'
WHEN M = '8' THEN 'Y'
WHEN M = '9' THEN 'Y'
WHEN M = '10' THEN 'Y'
WHEN M = '11' THEN 'Y'
WHEN M = '12' THEN 'Y'
END AS Yr, M, EnteredDateTime
FROM
(
SELECT DISTINCT 
                      SName,DatePart(year, EnteredDateTime) AS Y, DatePart(month, EnteredDateTime) AS M, EnteredDateTime
FROM         ORDERS                                   
) 
table1
ORDER BY SNAME

Open in new window

0
Comment
Question by:RecipeDan
[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 39

Expert Comment

by:appari
ID: 38816921
try this

SELECT SName, 
CASE
WHEN M = '1' THEN Y+1
WHEN M = '2' THEN Y+1
WHEN M = '3' THEN Y+1
WHEN M = '4' THEN Y
WHEN M = '5' THEN Y
WHEN M = '6' THEN Y
WHEN M = '7' THEN Y
WHEN M = '8' THEN Y
WHEN M = '9' THEN Y
WHEN M = '10' THEN Y
WHEN M = '11' THEN Y
WHEN M = '12' THEN Y
END AS Yr, M, EnteredDateTime
FROM
(
SELECT DISTINCT 
                      SName,DatePart(year, EnteredDateTime) AS Y, DatePart(month, EnteredDateTime) AS M, EnteredDateTime
FROM         ORDERS                                   
) 
table1
ORDER BY SNAME 

Open in new window

0
 
LVL 12

Expert Comment

by:Tony303
ID: 38816930
Is it because M and Y are integers not string

Try this


SELECT SName,
CASE
WHEN M = 1 THEN Y+1
WHEN M = 2 THEN Y+1
WHEN M = 3 THEN Y+1
WHEN M = 4 THEN Y
WHEN M = 5 THEN Y
WHEN M = 6 THEN Y
WHEN M = 7 THEN Y
WHEN M = 8 THEN Y
WHEN M = 9 THEN Y
WHEN M = 10 THEN Y
WHEN M = 11 THEN Y
WHEN M = 12 THEN Y
END AS Yr, M, EnteredDateTime
FROM
(
SELECT DISTINCT
                      SName,DatePart(year, EnteredDateTime) AS Y, DatePart(month, EnteredDateTime) AS M, EnteredDateTime
FROM         ORDERS                                  
)
table1
ORDER BY SNAME
0
 
LVL 18

Expert Comment

by:deighton
ID: 38817827
how about

SELECT SName,
CASE
WHEN M < 4 THEN
 Y+1
ELSE
 Y
END AS Yr, M, EnteredDateTime
FROM
(
SELECT DISTINCT
                      SName,DatePart(year, EnteredDateTime) AS Y, DatePart(month, EnteredDateTime) AS M, EnteredDateTime
FROM         ORDERS                                  
)
table1
ORDER BY SNAME 

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 500 total points
ID: 38819015
Hi,

Do you actually need to show Jan of 2013 as 2014 and May of 2013 as 2013?
I don't know what you are trying to do but if you need to get something like a fiscal year , then your logic is wrong. If it is something else you are trying tto do then ok.

If by any chance you would like to get the fiscal year (which in your cas seems to start on April, then the following would give you the result expected:

SELECT	DISTINCT
		SName,
		DatePart(year, DATEADD(M,-3,EnteredDateTime)) AS Y, --Get the Fiscal Year
		DatePart(month, EnteredDateTime) AS M, --Get the actual month
		EnteredDateTime --Get the actual Date
FROM    ORDERS                

Open in new window


Giannis
0
 
LVL 1

Author Comment

by:RecipeDan
ID: 38819205
Hi Giannis...that is what I am trying to do and your logic works great. Thank you for your reply to my problem.

Dan
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40989060
RecipeDan - I just kicked out an article that deals specifically with Fiscal calendar planning --> SQL Server Calendar Table:  Fiscal Years.   Let me know if this helps you, and if yes please click on the 'Good Article' button and provide some feedback.  Thanks.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

749 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