syntax error in case statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

syntax error in case statement

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-05-24 : 16:20:30

I am keep getting an incorrect syntax near case statement.
what I am doing wrong? or is there any better solutuion?

Declare @month int
Declare @prev_month int
Declare @year int
Declare @unit_abbr varchar(12)

set @month = 4
set @prev_month = @month -1
set @year = 2006
set @unit_abbr = 'ADS'

select
( case @month when 1 then 'Jar'
2 then 'Feb'
3 then 'Mar'
4 then 'Apr'
5 then 'May'
6 then 'Jun'
7 then 'Jul'
8 then 'Aug'
9 then 'Sep'
10 then 'Oct'
11 then 'Nov'
12 then 'Dec'

end ) as 'Month1',
a.gr1 as gr1,

( case @prev_month when 1 then 'Jar'
2 then 'Feb'
3 then 'Mar'
4 then 'Apr'
5 then 'May'
6 then 'Jun'
7 then 'Jul'
8 then 'Aug'
9 then 'Sep'
10 then 'Oct'
11 then 'Nov'
12 then 'Dec'

end ) as 'Month2',

b.gr2 as gr2
from
(
select sum(gross_revenue) as gr1 from revenue_aggregate
where month = @month and year = @year and unit_abbr = @unit_abbr

)a

,
(
select sum(gross_revenue) as gr2 from revenue_aggregate
where month = @prev_month and year = @year and unit_abbr = @unit_abbr
) b

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-05-24 : 17:21:46

I figure it out, but do yo uguys have better idea?
what I am trying to do is if the user enter the month it will return 4 months of data.

For example,

if the user enter 4
then it will returns 4,3,2,1 ( month) and data
if the user enter 3
it will return 3,2,1,12( month) and data
if the user enter 2
it will return 2,1,12,11 and data
and so on...

Is there better way to do this instead of using case statement and manually hard code the calculation?

Declare @month int
Declare @prev_month int
Declare @year int
Declare @unit_abbr varchar(12)

set @month = 4
set @prev_month = @month -1
set @year = 2006
set @unit_abbr = 'A08'

select
( case when @month = 1 then 'Jar'
when @month =2 then 'Feb'
when @month = 3 then 'Mar'
when @month = 4 then 'Apr'
when @month = 5 then 'May'
when @month = 6 then 'Jun'
when @month = 7 then 'Jul'
when @month = 8 then 'Aug'
when @month = 9 then 'Sep'
when @month = 10 then 'Oct'
when @month = 11 then 'Nov'
when @month = 12 then 'Dec'

end ) as 'Month1',
a.gr1 as gr1,

( case when @prev_month = 1 then 'Jar'
when @Prev_month =2 then 'Feb'
when @prev_month = 3 then 'Mar'
when @prev_month = 4 then 'Apr'
when @prev_month = 5 then 'May'
when @prev_month = 6 then 'Jun'
when @prev_month = 7 then 'Jul'
when @prev_month = 8 then 'Aug'
when @prev_month = 9 then 'Sep'
when @prev_month = 10 then 'Oct'
when @prev_month = 11 then 'Nov'
when @prev_month = 12 then 'Dec'

end )as 'Month2'
,b.gr2 as gr2
from
(
select sum(gross_revenue) as gr1 from revenue_aggregate
where month = @month and year = @year and unit_abbr = @unit_abbr

)a

,
(
select sum(gross_revenue) as gr2 from revenue_aggregate
where month = @prev_month and year = @year and unit_abbr = @unit_abbr
) b

Liza
Starting Member

11 Posts

Posted - 2006-05-24 : 18:14:51

Hi There,

Couldn't you just do something with the datediff function? I am not sure if you can pass a parameter into it but it seems like it would be a lot easier.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-24 : 18:19:13

Looks like you are doing a date cross tab report. The trick is to create a table with the dates you need, and join your data table to that.

If you do this a lot, you should create a date table to make this easier. The function on this link, F_TABLE_DATE, will load a date table for you.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

You can get other information about dates in SQL Server on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762


declare	@year int, @month int
declare @unit_abbr varchar(12)

-- Set Current Year and Month
select	@year=2006, @month=12
set @unit_abbr = 'A08'

-- Table to hold year/months
declare @months table ( 
num int not null,
year int not null,
month int not null,
primary key clustered  (year,month) 
)
-- Load Year month combinations
insert into @months
select
	b.num,
	[Year] = year(dateadd(month,b.num,a.DT )),
	[Nonth] = month(dateadd(month,b.num,a.DT ))
from
	( select DT = dateadd(month,((@year-1900)*12)+@month-1,0) ) a
	cross join
	( select num = 0 union all select -1 union all 
	  select -2 union all select -3 ) b
order by
	1,2



-- Create cross tab of Gross Revenue by year/month
select
	GR1 = sum(case when b.num = -3 then gross_revenue else 0 end),
	GR2 = sum(case when b.num = -2 then gross_revenue else 0 end),
	GR3 = sum(case when b.num = -1 then gross_revenue else 0 end),
	GR4 = sum(case when b.num =  0 then gross_revenue else 0 end)
from
	revenue_aggregate a
	join
	@months b
	on	a.year  = b.year   and
		a.month = b.month
where 
	a.unit_abbr = @unit_abbr

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources