They only way of knowing that that date was the last promotion is
because the previous record has a different job level. So if the next
record has a different job level then it would be a promotion (or
demotion).

Well the other records that are getting inserted into this table are pay
rate changes, and you can get pay increases even if you don't change job
levels. That is how you can get three records in a row of the same job
level. Yes it is bad design but I can't do anything about it. I am
just trying to figure out promotion dates with what I have been given.

Jeff

-----Original Message-----
From: Parveen Kumar
[mailto:oracledba-ezmlmshield-x12110247.[Email address protected]
Sent: Wednesday, May 16, 2007 9:10 PM
To: LazyDBA Discussion
Subject: Re: sql help

If anyone looks on the table and records in it ,

355 2/7/2005 450
355 8/18/2006 440
355 11/25/2006 440
355 2/7/2007 440

How can you tell anyone even oracle that the last promotional date was
8/18/2006 , May be I am not getting you but the date 8/18/2006 doesn't
make any sense to me by seing records in the table (which option tells
you that this is the last promotional data , please let me know then
only i can understand... ) if you add some records in the same table
structure then i don't believe the company will be able to promote any
more employees (or atleast get the actual promotion date) :)

How can one say that if add some other records in the table they are not

promotional records (if they are not then why you are inserting these
another records in the same table )
Parveen
Jeff Chirco wrote:
> Sorry it may have been a bit confusing. The last promotion date is
> 8/18/2006 when they went from job 450 to job 440. The other two more
> recent records are not really promotions, just other transactions that
> happened on the table. I know I know, the table was poorly designed a
> long time ago, but I am stuck with it, at least for now. One day it
> will change.
> Thanks for your help.
>
> -----Original Message-----
> From: Parveen Kumar
> [mailto:oracledba-ezmlmshield-x84356942.[Email address protected]
> Sent: Wednesday, May 16, 2007 5:14 PM
> To: LazyDBA Discussion
> Subject: Re: sql help
>
> I am not bit clear about the result you want then
>
> if you have another record for job 440 then for empid 355 last
promoted
> date should be 11/25/2006 and these queries will return the same value

> .So what actually you want , you want 8/18/2006 in all cases (these
> queries will return second highest dates i.e last promoted for an
> employee) , tell me if i am wrong here
>
> Parveen
>
>
> Jeff Chirco wrote:
>> I couldn't figure out how to get #1 to work.
>> But #2 is close but imagine if I had another record for job 440. Like
> so
>> 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
>>
>> 355 2/7/2007 440
>>
>> -----Original Message-----
>> From: Parveen Kumar
>> [mailto:oracledba-ezmlmshield-x65798821.[Email address protected]
>> Sent: Wednesday, May 16, 2007 4:58 PM
>> To: LazyDBA Discussion
>> Subject: Re: sql help
>>
>> why don't you try these
>>
>> 1)
>>
>> select level,max(sal) from emp
>> where level=2
>> connect by prior sal>sal
>> group by level;
>>
>> here connect by is special construction in Oracle to view data in
tree
>
>> manner and can take the values any values from any level..
>>
>> best of luck
>>
>>
>> 2) select max(sal) from where sal != (select max(sal) from emp)
>>
>> Parveen
>>
>> Chris wrote:
>>> I was a bit hasty
>>> I think this is it....
>>>
>>> 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 <= (select max(y.date)
>>> from prod..ps_job y
>>> where y.job = 450))
>>>
>>>
>>> -----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
>>>
>>>
>>>
>>>
>>
>>
>> ---------------------------------------------------------------------
>> 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
>>
>>
>
>
>
> ---------------------------------------------------------------------
> 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
>
>



---------------------------------------------------------------------
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