SQL Case Statement Syntax issue

[Vogel515]

select

fp.awarded_start_date,
case fp.awarded_start_date
when (fp.awarded_start_date >= '10/1/2006') then
case '9/30/2007'
when '9/30/2007' <= fp.awarded_end_date then
DATEDIFF('m','9/30/2007',fp.awarded_start_date,"m")
when '9/30/2007' > fp.awarded_start_date then
DATEDIFF('m',fp.awarded_start_date, fp.awarded_end_date, "m")
end
when fp.awarded_start_date < '10/1/2006'
case '9/30/2007'
when '9/30/2007' >= fp.awarded_end_date then
datediff('m',fp.awarded_end_date, '10/1/2006')
when '9/30/2007' < fp.awarded_end_date then
datediff('m','10/1/2006','9/30/2007')
end
end

What's wrong with my when statements?

thanks in advance!

 

[Markbnj]

You don't say what platform, but in SQL Server (Transact SQL) you don't repeat the input expression. For example...

case col_1
when 'A' then ...
when 'B' then ...
end

OR

case
when col_1 > a then ...
when col_1 > b then ...
end

 

[Vogel515]

Thanks for the response - I am using SQL Server 2000

solution:

case
when fp.awarded_start_date >= '10/1/2006' then
case
when '9/30/2007' <= fp.awarded_end_date then
DATEDIFF(mm,fp.awarded_start_date,'9/30/2007')
when '9/30/2007' > fp.awarded_start_date then
DATEDIFF(mm,fp.awarded_start_date, fp.awarded_end_date)
end
when fp.awarded_start_date < '10/1/2006' then
case
when '9/30/2007' >= fp.awarded_end_date then
datediff(mm, '10/1/2006',fp.awarded_end_date)
when '9/30/2007' < fp.awarded_end_date then
datediff(mm,'10/1/2006','9/30/2007')
end
end