• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

Case Statement

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
RecipeDan
Asked:
RecipeDan
1 Solution
 
appariCommented:
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
 
Tony303Commented:
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
 
deightonprogCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ioannis ParaskevopoulosCommented:
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
 
RecipeDanAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now