Fairfield
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
SELECT [Material Number], [Global Event Code] AS [Global Event], [Planned Date]
FROM dbo.LCGBEV
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())