Calculating from one record to another

Discussion created by dvhoudek on Jan 30, 2012
Latest reply on Jan 30, 2012 by dvhoudek

Is there a way to calculate the inclusive dates between two records in a table (or even in a related table if that makes it easier)? I need to calculate the dates between "effective_date" for someone's pay so I can check to see if a time record falls within the effective period for a specific pay record. For example, I may have four pay records, each with an effective date and I need to see if the project time I entered falls between the effective date of record 3 and 4 or 4 and today for a given employee. Unfortunately, I have no "end dates" on pay records. A scripted solution would be fine, but I'd rather see if there's a way to do it with calculations.