Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Lawson History Table help

Status
Not open for further replies.
Bronnad

Bronnad

Programmer
Jul 2, 2012
16
US
I am running a report that pulls from a history table.
I have 2 fields...

1- salary on 2/27/2012 (date of merit increase)

if {Salary History.BEG_DATE} = #2/27/2012#
then
if{Salary History.SALARY_CLASS}="Hourly"
then{Salary History.PAY_RATE}*{Salary History.ANNUAL_HOURS}
else{Salary History.PAY_RATE}

2- salary before 2/27/2012 (salary before merit increase)

if {Salary History.BEG_DATE} < #2/27/2012#
then
if{Salary History.SALARY_CLASS}="Hourly"
then{Salary History.PAY_RATE}*{Salary History.ANNUAL_HOURS}
else{Salary History.PAY_RATE}

Your assistance would be greatly appreciated.
 
Sort by date Sort by votes
Sorry about that...
Both formulas give me the salary that is listed on 2/27/2012
 
Upvote 0 Downvote
Afer a refresh I get the correct salary for

if {Salary History.BEG_DATE} = #2/27/2012#
then
if{Salary History.SALARY_CLASS}="Hourly"
then{Salary History.PAY_RATE}*{Salary History.ANNUAL_HOURS}
else{Salary History.PAY_RATE}

but I get 0.00 for the other formula

if {Salary History.BEG_DATE} < #2/27/2012#
then
if{Salary History.SALARY_CLASS}="Hourly"
then{Salary History.PAY_RATE}*{Salary History.ANNUAL_HOURS}
else{Salary History.PAY_RATE}

 
Upvote 0 Downvote
Yes.

I think I may have worked it out by using the "maximum" summary, and changeing if {Salary History.BEG_DATE} < #2/27/2012# to
if {Salary History.BEG_DATE} <= #2/27/2012# in order to capture those who did not get a merit increase.

 
Upvote 0 Downvote
Nope, not working.

The maximum works for the 2/27/2012 date, but in < 2/27/2012 I get the maximum.

So, if someone gets 2 merit increases in 1 day (rare, but happens) I get the greater of the 2.
I have an employee that went from salary to hourly (salary was an error), it should be giving me their hourly amount, but since the salary amount is greater, I'm getting that.

I tried a Numbervar, but It's not giving me the most recent amount prior to 2/27/2012.
 
Upvote 0 Downvote
Remove your "else" clauses in each formula, so that the default is 0. Then apply maximums.

-LB
 
Upvote 0 Downvote
Ibass...

Thanks, but it didn't work.

There are some employees that have salary changes on the same day. there is one case where someone received a salary increase and a few hours later was changed to hourly.

In that case the salary is the maximum (50,000), but the hourly (25.64) is the correct amount, and also the hourly is the most recent amount for the below formula.

if {Salary History.BEG_DATE} < #2/27/2012#
then
if{Salary History.SALARY_CLASS}="Hourly"
then{Salary History.PAY_RATE}*{Salary History.ANNUAL_HOURS}
else{Salary History.PAY_RATE}

But, the formula is giving me the salary.

Just to refresh...
Here is the date range:
{PRRATEHIST.BEG_DATE} in DateTime (2008, 01, 01, 00, 00, 00) to DateTime (2012, 02, 27, 00, 00, 00)


 
Upvote 0 Downvote
Please show sample data (multiple rows) that illustrates the problem.

-LB
 
Upvote 0 Downvote
Here goes...


Emp ID BEG_DAT PAY_RATE
XYZ 03/24/08 50,000.00
XYZ 03/24/08 50,000.00
XYZ 03/24/08 50,000.00
XYZ 03/24/08 50,000.00
XYZ 02/15/09 52,400.00
XYZ 02/15/09 52,400.00
XYZ 02/15/09 52,400.00
XYZ 02/15/09 52,400.00
XYZ 02/13/11 53,400.00
XYZ 02/13/11 53,400.00
XYZ 02/13/11 53,400.00
XYZ 02/13/11 53,400.00
XYZ 11/20/11 50,000.00
XYZ 11/20/11 50,000.00
XYZ 11/20/11 50,000.00
XYZ 11/20/11 50,000.00
XYZ 11/20/11 25.64
XYZ 11/20/11 25.64
XYZ 11/20/11 25.64
XYZ 11/20/11 25.64
XYZ 02/27/12 26.15
XYZ 02/27/12 26.15
XYZ 02/27/12 26.15

For the formula that is less than 2/27/2012 I want it to display the most recent amount (i.e. 11/20/2011 25.64)
 
Upvote 0 Downvote
What you are trying to do in Crystal is very difficult. I would use a view of the salary table or if you can build a command for whole report incorporating a query like this

I assume that your Beg_date is actually a datetime if not how do you know what is last position

Select s.* from salaryTable S
inner join(
Select Emp_ID, Max(BEG_DAT) Max_date
From salaryTable
group by Emp_ID ) SD
on s.Emp_id = sd.Emp_id and s.Beg_date = sd.max_date

the above would return something like, which will make you report mmuch easier to construct

XYZ 03/24/08 50,000.00
XYZ 02/15/09 52,400.00
XYZ 02/13/11 53,400.00
XYZ 11/20/11 25.64
XYZ 02/27/12 26.15

Ian
 
Upvote 0 Downvote
Why is this not working?

if {Salary History.BEG_DATE} = #2/27/2012#
then
if{Salary History.SALARY_CLASS}="Hourly"
then{Salary History.PAY_RATE}*{Salary History.ANNUAL_HOURS}
else{Salary History.PAY_RATE}

else

if {Salary History.BEG_DATE} < #2/27/2012#
then
if{Salary History.SALARY_CLASS}="Hourly"
then{Salary History.PAY_RATE}*{Salary History.ANNUAL_HOURS}
else{Salary History.PAY_RATE}

It seems so simple...if there is a salary amount listed on 2/27/2012 show it, if not give me the amount shown for the closest previous date.
 
Upvote 0 Downvote
How do you know which is the most recent record? Is the date field really a date time? Is there another field that indicates the order in which the records were entered? The record order is not depends upon your sort, so what are you sorting on that tells you that 25.64 is the most recent record before the given date?

-LB
 
Upvote 0 Downvote
In laswon there is a salary history table. In there is where the dates and salary increases are located.
I check in there to see if the records a pulling up correctly.
 
Upvote 0 Downvote
Sorry, I may have misunderstood.

I did a data dump of the table with a select date range of 1/1/2008 - 2/27/21

I used 5 employees. All data was in the details, not sorting done.
The results came back with the data I required. No salaries with a date higher then 2/27/2012.
 
Upvote 0 Downvote
If you sorted on one of the fields, you could not tell what is the most recent record. So what field do you have that would indicate which row is the most recent? It might be a datetime, a time field, a record number, etc.

-LB
 
Upvote 0 Downvote
I hope this helps.


EMPL BEG_DAT Annual PAY_RATE SUB Pre-Merit SUB Post-Merit
XYZ 02/17/08 42,905.66 22.00 42,905.66 46,884.24
XYZ 02/15/09 44,192.85 22.66 44,192.85 46,884.24
XYZ 02/13/11 45,518.66 23.34 45,518.66 46,884.24
XYZ 02/27/12 46,884.24 24.04 0.00 0.00
42,905.66 0.00

It's sorted by employee, the Annual is the amount on the BEG_DAT.
Under the pre-merit the amount should be 45,518.66 ( < 2/27/2012).
Under post-metit the amount should be 46,884.24 (<= 2/27/2012).

 
Upvote 0 Downvote
I created these formulas to pull the data....

Main Pre-Merit...
Shared Numbervar MyPreMerit;
MyPreMerit;

SUB Pre-Merit...
Shared Numbervar MyPreMerit;

if date({PRRATEHIST.DATE_STAMP}) < #2/27/2012#

then

if
({PRRATEHIST.SALARY_CLASS}="H")
then
{PRRATEHIST.PAY_RATE}*{PRRATEHIST.ANNUAL_HOURS}
else{PRRATEHIST.PAY_RATE}

and the CLEAR...
Shared Numbervar MyPreMerit;
MyPreMerit := ({@Annual});

the @Annual formula is...
if{PRRATEHIST.SALARY_CLASS}="H"
then{PRRATEHIST.PAY_RATE}*{PRRATEHIST.ANNUAL_HOURS}
else{PRRATEHIST.PAY_RATE}

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bbranco
  • Locked
  • Question
Query Help - Group By 2 tables
Replies
0
Views
156
bbranco
bbranco
JustMe24
  • Locked
  • Question
How to select from 2 rows in a table
Replies
1
Views
241
SoftwareRT
SoftwareRT
smmt2207
  • Locked
  • Question
Text field formula help
Replies
1
Views
242
hilfy
hilfy
PLOtt
  • Locked
  • Question
Help with publication...
Replies
1
Views
146
CrystalKiwi
CrystalKiwi
MusicMan123
  • Locked
  • Question
Record Selection Formula Help
Replies
0
Views
118
MusicMan123
MusicMan123

Part and Inventory Search

Sponsor

Back
Top