• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

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