I have a problem with the following scenario:
I have Transactions table, Employees table and Insurance table (for insurance value), the transaction table contains the transactions that happened on employee families, for example when an employee get married then there is a transaction should be entered to the table and set the wife value 1 instead of 0 and when he get a boy also and so on.
the problem is I want to calculate the insurance cost for childs that calculated by (NO_CHILDS * CHILD_INSURANCE) for march,2011 and there is an employee has 2 records in that month, the first record displayed that he has a new born child, and the second record displays that one of the two children became an adult.
we need to come up with a calculation for the last record which displays 1 child only.
please see the attached example.
Yousef Amarneh
