CASE Statement

Hello,
I am trying to understand how to use CASE statements within a CASE
statement. I am using the following but getting the error:
Incorrect syntax near the keyword 'CASE'.
declare @.MyDate DATETIME, @.YearType varchar(255)
set @.MyDate = getdate()
set @.YearType = 'fiscal'
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
Convert(varchar,year(@.MyDate))
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
Convert(varchar,year(@.MyDate))
END
Any help would be appreciated.
Thanks in advance,
sck10
You are missing END in the inner CASEs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sck10" <sck10@.online.nospam> wrote in message news:upANGW6XFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am trying to understand how to use CASE statements within a CASE
> statement. I am using the following but getting the error:
> Incorrect syntax near the keyword 'CASE'.
> declare @.MyDate DATETIME, @.YearType varchar(255)
> set @.MyDate = getdate()
> set @.YearType = 'fiscal'
> Select
> CASE @.YearType
> WHEN 'fiscal' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
> Convert(varchar,year(@.MyDate))
> WHEN 'calendar' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate))
> END
>
> Any help would be appreciated.
> --
> Thanks in advance,
> sck10
>
|||On Mon, 23 May 2005 09:41:35 -0500, sck10 wrote:

>Hello,
>I am trying to understand how to use CASE statements within a CASE
>statement. I am using the following but getting the error:
>Incorrect syntax near the keyword 'CASE'.
Hi sck10,
Tibor already found the missing END's, but he seems to have missed the
extraneous CASE's.
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
ELSE 'Q04' + Convert(varchar,year(@.MyDate))
END
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
ELSE 'Q04-' + Convert(varchar,year(@.MyDate))
END
END
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)