SQL query and History records in F07042

pbare

Well Known Member
Hi All,

I need some help with writing a SQL statement that will retrieve the current record and the previous record from the history table.

I am trying to determine when we have employees that change WCB and/or Province of Work in the current year

Here is my current query
select jwan8, jwefto, jwhstd as CurrValue, lag(jwhstd,1,0) over (order by jwefto desc) as prevValue
from proddta.f08042
where jwfile = 'F060116'
and jwan8 =901670
and jwdtai = 'TARA'
and jwtrs != '001'


This gives me the following result
AN8 EFTO HSTD Prev Value
901670 118060 700010000 0
901670 117255 700210000 700010000
901670 103001 700010000 700210000


What I would ultimately like to see is
AN8 EFTO HSTD Prev Value
901670 118060 700010000 700210000

I want to see the most current value along with the previous value

We are working on converting our JDE data (9.1) to a cloud based HCM system.

Any help would be appreciated
Thanks
Pam
 

peterbruce

Legendary Poster
Pam,

Thought it is a little complex, the SQL below should give you what you need :

select a.jwan8, a.jwefto, a.jwhstd as CurrValue,
nvl((select c.jwhstd from proddta.f08042 c
where c.jwan8 = a.jwan8 and c.jwfile = a.jwfile
and c.jwdtai = a.jwdtai and c.jwtrs = a.jwtrs
and c.jwefto = (select max(b.jwefto)
from proddta.f08042 b
where b.jwan8 = a.jwan8 and b.jwfile = a.jwfile
and b.jwdtai = a.jwdtai and b.jwtrs = a.jwtrs and b.jwefto < a.jwefto)
),0) as prevValue
from proddta.f08042 a
where a.jwfile = 'F060116'
and a.jwan8 =901670
and a.jwdtai = 'TARA'
and a.jwtrs != '001'
and a.jwefto = (select max(b.jwefto)
from proddta.f08042 b
where b.jwan8 = a.jwan8 and b.jwfile = a.jwfile
and b.jwdtai = a.jwdtai and b.jwtrs = a.jwtrs)
 

pbare

Well Known Member
Thanks

I tried the query but Prev value is always 0

I will see if I can tweak the query to provide me with the value

Pam
 

Kim Schmidt

Active Member
My two cents worth:
First, you might be getting the 0 for the previous value if a different reason code was used - the old record may be for a new hire and the latest for a genuine move to a different location. Try Peter's query without the JWTRS joins, or select using a list of reason codes.
Also, some HR site procedures result in multiple rows with the same effective date to be created, like when a person loads a supervisor change than a different person enters the corresponding pay rate change. Or they don't clean up the table (using P050420 for instance) when a wrong entry is made. Instead of using the max(jwefto), try using the sub-queries with "order by jwefto desc, jwseq# desc" and use a clause to select only the first row. The first row selection syntax of course is different depending on your database - SQL Server uses "select top 1 from...", while DB2 and Oracle (starting with 12c) use "...fetch first 1 rows only".
Good luck!
 
Top