Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

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.
0
Fairfield
Asked:
Fairfield
1 Solution
 
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
 
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
 
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now