Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 621
  • 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
 
deightonCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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