J
John.Forr
I'm having some trouble trying to figure out how to prorate a payment
amount based on dates.
My table structure looks like this:
Payperiods:
PayPeriodID (autonumber)
PayPeriodStart (Date)
PayPeriodEnd (Date)
PaymentAmounts:
PaymentID (Autonumber)
PersonID (FK)
EffectiveDate (Date)
PaymentAmount (Currency)
Each person will have multiple payment amounts. Each time their pay
changes, a new entry would be made with the effective date. For
calculating their net pay for a specific period though, I need to
prorate their pay amount if a change occured in the middle of the pay
period.
Also, two other things making this a tad easier: I only need to
calculate the prorated pay for the current payperiod. Another query
calculates the current payperiod which can be joined to the table
Payperiods limiting that table to just one record. The 2nd item: it
can be assumed that a particular person would only have their pay
amount change once within a single payperiod.
What I'm looking for would be the Prorated Pay Amount grouped by
salesperson and PayperiodID
amount based on dates.
My table structure looks like this:
Payperiods:
PayPeriodID (autonumber)
PayPeriodStart (Date)
PayPeriodEnd (Date)
PaymentAmounts:
PaymentID (Autonumber)
PersonID (FK)
EffectiveDate (Date)
PaymentAmount (Currency)
Each person will have multiple payment amounts. Each time their pay
changes, a new entry would be made with the effective date. For
calculating their net pay for a specific period though, I need to
prorate their pay amount if a change occured in the middle of the pay
period.
Also, two other things making this a tad easier: I only need to
calculate the prorated pay for the current payperiod. Another query
calculates the current payperiod which can be joined to the table
Payperiods limiting that table to just one record. The 2nd item: it
can be assumed that a particular person would only have their pay
amount change once within a single payperiod.
What I'm looking for would be the Prorated Pay Amount grouped by
salesperson and PayperiodID