Re: data comparsion

["Beller, Jay" <jbeller@LBISOFTWARE.COM>]

Assuming that you want a SIMPLE solution:

Create one SELECT that retrieves the fields for max(effdt), making sure to 
select effdt. Then using that info, perform a second SELECT "less than effdt" 
and only run it once. For example,

begin-select
j1.empnbr
j1.effdt
j1.annual_salary

        do read-prior
        let #current_salary = &j1.annual_salary
        let #net_adj = #current_salary / #prior_salary * .02

from job_salary j1
where j1.effdt = (select max(j2.effdt)
                   where j1.empnbr=j2.empnbr)
end-select

...
begin-procedure read-prior

! LOOPS=1 combined with descending effdt gets you the prior record

begin-select loops=1
prior.annual_salary

        let #prior_salary = &prior.annual_salary

from job_salary prior
where prior.empnbr = &j1.empnbr
  and prior.effdt < &j1.effdt
order by prior.effdt desc
end-selectend-procedure read-prior

-----Original Message-----
From: Lorraine Ambrosio [mailto:lorraine.ambrosio@TECKCOMINCO.COM]
Sent: Friday, April 19, 2002 9:23 PM
To: SQR-USERS@list.iex.net
Subject: data comparsion


I want to compare for the same person, the most recent date/salary with the
last prior record. How do I get the 2nd record?


eg. fields
empnbr  eff_date    annual_salary

12345   01-Apr-2002  50,000  max(eff_date)
12345   01-Apr-2001  40,000  ???

then take 50,000 / 40,000 * .02 / 9/14 = net adj.