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.

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.

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

1 Solution

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

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

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

Dan

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.

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

Join Now
Open in new window