RecipeDan
asked on
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.
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Giannis...that is what I am trying to do and your logic works great. Thank you for your reply to my problem.
Dan
Dan
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.
Open in new window