Determine status based on date

Here is an example of some data I have in a table.

PN             CODE       DATE
----------    ---------    ------------
12233        ES           09/01/2009
12334        ES           03/02/2009
12331        SA          01/06/2009  
12233        SA          01/03/2009
12233        PA          02/01/2009

The codes are:

ES - Obsolete
SA- Active1
PA - Active2

What I would like is a function that looks at the uniqe PN and gives me the current status based on today's date.  For example, 12233 should give me a status of PA since it hasn't reached a status of ES yet.
FairfieldAsked:
Who is Participating?
 
tigin44Commented:
this will give you what you want
SELECT Y.PN, Y.CODE, Y.DATE
FROM yourTable Y 
		INNER JOIN (SELECT PN, MAX(DATE) AS DATE
					FROM yourTable
					WHERE date <= GETDATE()
					GROUP BY PN) A ON Y.PN = A.PN AND Y.DATE = A.DATE 

Open in new window

0
 
chapmandewCommented:
this should do it:

create table #s(pn int, es char(2), dt datetime)

insert into #s
select 12233  ,      'ES'    ,       '09/01/2009' union all
select 12334   ,     'ES'   ,        '03/02/2009' union all
select 12331    ,    'SA'  ,        '01/06/2009'   union all
select 12233     ,   'SA' ,         '01/03/2009' union all
select 12233      ,  'PA',          '02/01/2009'

The codes are:

ES - Obsolete
SA- Active1
PA - Active2

What I would like is a function that looks at the uniqe PN and gives me the current status based on today's date.  
For example, 12233 should give me a status of PA since it hasn't reached a status of ES yet.
Tags:
Add Tags



select *
from #s s
where dt = (select max(dt) from #s t where s.pn = t.pn and dt < getdate())
0
 
FairfieldAuthor Commented:
How will this work with the following table and fields, I can't seem to get it to work.

SELECT     [Material Number], [Global Event Code] AS [Global Event], [Planned Date]
FROM         dbo.LCGBEV
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.