Next Functions and Aggregate Functions

[HelenNY]

I have a table with pay rates and effective dates in it and I need to calculate the total pay for the period based on the effective dates. Here's an example of the data for an employee:

Employee, Effective Date, Amount
1234 , 01/01/2011 , 50.00
1234 , 03/01/2011 , 52.00
1234 , 06/01/2011 , 55.00

I need to be able to get the Next Value of the effective date in order to calculate the pay amount for the date range. So for the first record, I would need to be able to get the start date of 3/1/2011, so I could calculate the pay between the days of 1/1/2011 and 2/28/2011. I can get the next start date of 3/1/2011 fine, but the problem is I can't perform any running totals or aggregate functions.
Any other ideas?

 

[lbass]

You can use running totals where you define the date range in the evaluation section->use a formula area:

{table.date} >= {table.effectivedate} and
{table.date} < next({table.effectivedate})

-LB

 

[HelenNY]

Thanks lbass, but I'm not sure I follow, or maybe I didn't clearly explain my question. I'm trying to get the net projected pay for the year. So for each record, I need to find the next pay rate's start date so I can calculate the total pay for each "period".

1234 , 01/01/2011 , 50.00
1234 , 03/01/2011 , 52.00
1234 , 06/01/2011 , 55.00

1. So for the first record, the total pay should be calculated as such: (days in range / days in year) * 50.00 * annual hours. So this would be (59 / 365) * 50.00 * 2080 = 16,810.96. That would give me the net pay between 1/1 and 3/1.

2. Then I would do the same for the next period 3/1 - 6/1. (92 / 365) * 52.00 * 2080 = 27,262.25.

3. Then finally for the last rate change 6/1 - end of year. (214 / 365) * 55.00 * 2080 = 67,072.88.

So the total pay for the year is 111,146.09. The problem is, I need the "next effective date" in my calculation for each line. I can get the next effective date fine and calculate the days in the range, but I can't use that value anywhere in my other calculations. Am I missing something obvious?

 

[lbass]

Use a variable like this:

//{@accum} for the detail section:
whileprintingrecords;
numbervar x;
numbervar annhrs := 2080;//hard value?
numbervar sumx;
if onlastrecord or
{table.employee} <> next({table.employee}) then
x := date(year({table.date}),12,31) - {table.date} else
x := next({table.date})-{table.date};
sumx := sumx + (x/365)*{table.amt}*annhrs;

//{@display} for the group footer (employee):
whileprintingrecords;
numbervar sumx;

//{@reset} for the group header (employee):
whileprintingrecords;
numbervar x;
numbervar sumx;
if not inrepeatedgroupheader then(
x := 0;
sumx := 0
);

-LB

 

[HelenNY]

LBASS - You rock!! I spent several hours trying to figure this out and couldn't get the totals working. Thank you so much!!!