?
Solved

Determine status based on date

Posted on 2009-05-06
3
Medium Priority
?
182 Views
Last Modified: 2012-05-06
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
Comment
Question by:Fairfield
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24317810
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
 
LVL 26

Accepted Solution

by:
tigin44 earned 2000 total points
ID: 24343086
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
 

Author Comment

by:Fairfield
ID: 24528654
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question