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

# 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
``````
0
RecipeDan
1 Solution

Commented:
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

Commented:
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

progCommented:

``````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
``````
0

Commented:
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
``````

Giannis
0

Author 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

Microsoft 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.

## Featured Post

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