Solved

Case Statement

Posted on 2013-01-24
6
594 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 66

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

626 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