Help with nested case - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help with nested case

Author

Topic

marwest98
Starting Member

9 Posts

Posted - 2009-03-30 : 08:49:21

I am trying to nest a case statement, but no matter how I do this, it does not want to work. I am new to SQLServer2005, so, be patient with me. I am building a temporary table containing just the periods I need in a stored procedure, so that I will be able to select 3 periods of data later on. The problem is, that when I go backwards to period 12 or 13, I also need to change the year. I tried just setting the year to what I need it to be, but it didn't like that either. Please help. A sample is below.

Select a.gl_cmp_key,
a.gl_perod_stdt,
a.gl_perod_enddt,
a.gl_perod_year,
a.gl_perod_id,
a.gl_perod_currp,
a.gl_perod_seqno,
b.gl_perod_stdt as gl_perod_prevstdt,
b.gl_perod_enddt as gl_perod_prevenddt,
b.gl_perod_id as gl_perod_previd,
b.gl_perod_currp as gl_perod_prevcurrp,
b.gl_perod_seqno as gl_perod_prevseqno
into dbo.temp_gl_acctperiodprev_tbl
from dbo.temp_gl_acctperiodcur_tbl as a
Inner Join gl_perod_tbl as b
on b.gl_cmp_key = b.gl_cmp_key
and a.gl_perod_year = b.gl_perod_year
and b.gl_perod_id =
case a.gl_perod_id
when '13' then '12'
case a.gl_perod_year
when '2009' then '2008'
when '2010' then '2009'
end
when '12' then '11'
when '11' then '10'
when '10' then '9'
when '9' then '8'
when '8' then '7'
when '7' then '6'
when '6' then '5'
when '5' then '4'
when '4' then '3'
when '3' then '2'
when '1' then '13'
end

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-30 : 08:54:17

What are you trying to do ? Post some sample data and output.

marwest98
Starting Member

9 Posts

Posted - 2009-03-30 : 09:03:17

In the statement below, after
the case a.gl_perod_id
when '13' then '12'

I am trying to add another case statement that says
case a.gl_perod_year
when '2009' then '2008'
when '2010' then '2009'

Select a.gl_cmp_key,
a.gl_perod_stdt,
a.gl_perod_enddt,
a.gl_perod_year,
a.gl_perod_id,
a.gl_perod_currp,
a.gl_perod_seqno,
b.gl_perod_stdt as gl_perod_prevstdt,
b.gl_perod_enddt as gl_perod_prevenddt,
b.gl_perod_id as gl_perod_previd,
b.gl_perod_currp as gl_perod_prevcurrp,
b.gl_perod_seqno as gl_perod_prevseqno
into dbo.temp_gl_acctperiodprev_tbl
from dbo.temp_gl_acctperiodcur_tbl as a
Inner Join gl_perod_tbl as b
on b.gl_cmp_key = b.gl_cmp_key
and a.gl_perod_year = b.gl_perod_year
and b.gl_perod_id =
case a.gl_perod_id
when '13' then '12'
when '12' then '11'
when '11' then '10'
when '10' then '9'
when '9' then '8'
when '8' then '7'
when '7' then '6'
when '6' then '5'
when '5' then '4'
when '4' then '3'
when '3' then '2'
when '1' then '13'
end

I need to do this to change back to a previous year when we are in period 1 of a new year. This is the record for period 1 of this year,

50 2009 1 2008-09-28 00:00:00.000 2008-10-25 23:59:59.000 N N ADAdmin 2008-12-16 00:00:00.000 2008-12-16 00:00:00.000 1 16 N N N N N N 49

and this is the record for period 13 of last year: There will be multiple period records, so I have to make sure I get the record for the correct year.

50 2008 13 2008-08-31 00:00:00.000 2008-09-27 23:59:59.000 N N ADAdmin 2008-12-16 00:00:00.000 2008-12-16 00:00:00.000 1 13 N N N N N N 10

Mary Westerman

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-30 : 09:19:35

I am not very clear on what you are trying to do.
May be you just want to change your join clause for gl year instead of adding nested ifs.

like,


		and  b.gl_perod_year = 	
			case a.gl_perod_year
				when ='2009' then '2008'
				when ='2010' then '2009'
				...
			end

OR instead of writing many case statements, use this,

and  b.gl_perod_year = 	convert(int,gl_perod_year)-1

marwest98
Starting Member

9 Posts

Posted - 2009-03-30 : 09:31:44

I can only change the year when the gl_perod_id is = 12 or 13, otherwise the what you have suggested would work. So, basically if the gl_perod_id = 13 then gl_perod_year = gl_perod_year -1. The issue is I can't seem to get the gl_perod_year changed only when gl_perod_id = 13 or 12 if I need to go back two periods.

Mary Westerman

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-30 : 09:39:12

This ???

and  convert(int,b.gl_perod_year) = 	
			case when 
				a.gl_perod_id in ('12','13') 
				then
					convert(int,gl_perod_year)-1			
				else 	
					convert(int,a.gl_perod_id)	
			end

marwest98
Starting Member

9 Posts

Posted - 2009-03-30 : 10:05:04

Didn't return anything at first, but, I made some modifications and it worked. Thanks!!

Mary Westerman

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-30 : 10:09:27

great !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources