How can I capture data and prior value in a sql query ?
709066Jun 26 2009 — edited Jul 2 2009Hello SQL experts,
I have what is probably an easy and basic question. I am trying to capture data for some rows that meet a date range and other criteria
and also capture a prior value that they had before the date range.
For example, I need to capture a large number of employees that got promotions during a certain time period which I can do just fine BUT
I also need to capture what their prior jobcode was before it got changed by their promotion. Any help in pointing me in the right
direction would be greatly appreciated.
My filtered basic SQL is:
Select emplid, empl_rcd, action, action_dt, effdt, jobcode
from ps_job where
action='prom' and effdt between '01-JAN-2009' and '01-JUN-2009'
The above is fine but I also need the prior jobcode value (before the promotion during the '01-JAN-2009' and '01-JUN-2009' date range)
I tried doing a Union but got way too many rows. Would a self join work ? Any help you can give me would be great! Thanks in advance for your time!
Jason