Case Statement

[renee35]

I am trying to get the previous quarter close date in the below case statement:

Here are the fields:

pdyear pdid closedt
2007 1 2007-4-18
2007 2 2007-07-18
2007 3 2007-10-24
2007 4 2008-1-28

The case statement would need to be such that:

Case when pdid = 2 then closedt for pdid 1 (I need to return 2007-4-18)?

That is what I am trying to achieve..


Thanks a bunch!!

-T

 

[PWise]

no need for case

Select closedt
from Tablename
where pdid =2

but how do you identify the previous quarter

 

[renee35]

I mean previous period in this case would be pdid 1 when say: when pdid = 2 then 'closedt of pdid = 1'

Thanks a bunch!!

-T

 

[logidude]

create table #tmp (pdYear int, pdid int, closeddt varchar(100))

insert into #tmp values (2008,1,'2007-4-18')
insert into #tmp values(2008,2,'2007-07-18')
insert into #tmp values (2008,3,'2007-10-24')
insert into #tmp values(2008,4,'2008-1-28')

select pdYear, pdID, case 
when pdID = 2 then (select closeddt from #tmp where pdID = 1)
when pdID = 3 then (select closeddt from #tmp where pdID = 2)
when pdID = 4 then (select closeddt from #tmp where pdID = 3)
else
NULL
end as x from #tmp

drop table #tmp
[code]

This works, of course not knowing your requirement I hard coded the values but you could adjust this to match a business rule.

 

[PWise]

Select Tablename.* ,pp.closedt
from Tablename
inner join Tablename as pp
on Tablename.pdid-1=pppdid

 

[logidude]

Ah yes pwise solution is much better, I was too stuck into answering your question about the case to think about it.

Use a left outer join if you need the ppID = 1 value of NULL.

 

[renee35]

Okay, thanks a bunch everyone..

Thanks a bunch!!

-T