Only if I had "somedate" to use.
-----Original Message-----
From: Chris
[mailto:oracledba-ezmlmshield-x4477235.[Email address protected]
Sent: Wednesday, May 16, 2007 4:48 PM
To: LazyDBA Discussion
Subject: RE: sql help
How about this one...
select *
from prod..ps_job j
where j.empid = 355
and job = 440
and j.date = (select max(x.date)
from prod..ps_job x
where x.empid = j.empid
and x.date <= somedate)
-----Original Message-----
From: Jeff Chirco
[mailto:oracledba-ezmlmshield-x77832440.[Email address protected]
Sent: Wednesday, May 16, 2007 4:44 PM
To: LazyDBA Discussion
Subject: RE: sql help
No that would return the last record for job 440 which is 11/25/2006. I
want to see 8/18/2006.
Thanks
Jeff
-----Original Message-----
From: Chris
[mailto:oracledba-ezmlmshield-x96819660.[Email address protected]
Sent: Wednesday, May 16, 2007 4:39 PM
To: LazyDBA Discussion
Subject: RE: sql help
Jeff-
Are you using ADP software?
Try something like this...
select *
from prod..ps_job j
where j.empid = 355
and j.date = (select max(x.date)
from prod..ps_job x
where x.empid = j.empid)
-----Original Message-----
From: Jeff Chirco
[mailto:oracledba-ezmlmshield-x13527297.[Email address protected]
Sent: Wednesday, May 16, 2007 4:30 PM
To: LazyDBA Discussion
Subject: sql help
Here is my dilemma. I have the table like below
EMPID DATE JOB
355 8/9/2003 450
355 9/18/2004 440
355 2/7/2005 450
355 8/18/2006 440
355 11/25/2006 440
What I need to get is somebody's last promotion date. So if I want to
see when this person was last promoted to job 440 I want to see
8/18/2006, not 11/25/2006 or 9/19/2004. Or if I want to see when this
person was last promoted to job 450 I would expect to see 2/7/2005. I
can't figure out how to query this data.
Thanks for any help.
Jeff
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.
Douglas County, Oregon
www.co.douglas.or.us
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.
Douglas County, Oregon
www.co.douglas.or.us
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page