Comparing the most recent row to the previous row

building reports desktopintelligence
1

In a message dated 99-04-15 08:15:40 EDT, you write:

I will need to write a query to compare the row of a table with the most
recent effective_date to the previous row. Specifically, I will be
querying
salary change rows and I’ll need to subtract the previous salary from the
current salary to get the salary change amount.

You might be able to do the calculation on the report. If you list the items
sorted by Emp Name followed by Change Date, then use the BusObj Previous()
function to do the trick.

The formula would be something like:

Variable: Salary Change
Formula:
If Previous() = Then - Previous()

The check to compare the two 's is to make sure that you don’t
substract Sally’s last salary from Fred’s first salary. The Previous()
function works a bit like a spreadsheet, in that it looks from row to row in
the table.

To create an object to do this would be very difficult. SQL Databases do not
have any concept of “previous”, “next”, “first”, or “last”. You would likely
have to create a custom database function (if available in your RDBMS) to do
the same calculation, or create a table specifically to support it.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

2

I will need to write a query to compare the row of a table with the most
recent effective_date to the previous row. Specifically, I will be
querying
salary change rows and I’ll need to subtract the previous salary from the
current salary to get the salary change amount.

Dave’s solution with variables is probably the best bet. But, I thought I
would mention a possible solution using SQL for the “previous salary”.
Perhaps you could create an object with SQL similar to this:

SELECT salary
FROM employee_history
WHERE employee_id = {employee id}
AND effective_date =
(SELECT MAX(effective_date)
WHERE employee_id = {employee id}
AND effective_date < {effective date})

Note - items in {} are objects in your universe. This SQL may not be a good
performer.

Dennis Edgecombe
Washington State University

Listserv Archives (BOB member since 2002-06-25)