Dear
Friends
Below is a table of customers with debts in given dates. The available amount is the only amount capable to pay their debts (Food, Rent).
For example Peter has 220 000 amount to pay his debts i.e Food 40000+40000+40000+10000 and rent 30000+30000+30000 whicch is equal to 220000)
The unpaid amount then shall be food 30000 and rent 30000.
For Emma the last customer he shall be able to pay the entire debt as available amount is greater that total debt.
My question is given available data how can I compute Unpaid Food and Unpaid rent as displayed below. I am using MS SQL
Your response shall be much appreciated.
| |
Dates |
AvailableAmount |
Food |
Rent |
Unpaid Food |
Unpaid Rent |
| Peter |
31/01/2003 |
220000 |
40000 |
30000 |
0 |
0 |
| Peter |
28/02/2003 |
220000 |
40000 |
30000 |
0 |
0 |
| Peter |
31/03/2003 |
220000 |
40000 |
30000 |
0 |
0 |
| Peter |
30/04/2003 |
220000 |
40000 |
30000 |
30000 |
30000 |
| Simon |
31/01/2003 |
120000 |
40000 |
30000 |
0 |
0 |
| Simon |
28/02/2003 |
120000 |
40000 |
30000 |
0 |
20000 |
| Simon |
31/03/2003 |
120000 |
40000 |
30000 |
40000 |
30000 |
| Simon |
30/04/2003 |
120000 |
40000 |
30000 |
40000 |
30000 |
| Edward |
31/01/2003 |
80000 |
40000 |
30000 |
0 |
0 |
| Edward |
28/02/2003 |
80000 |
40000 |
30000 |
30000 |
30000 |
| Edward |
31/03/2003 |
80000 |
40000 |
30000 |
40000 |
30000 |
| Edward |
30/04/2003 |
80000 |
40000 |
30000 |
40000 |
30000 |
| John |
31/01/2003 |
85000 |
40000 |
30000 |
0 |
0 |
| John |
28/02/2003 |
85000 |
40000 |
30000 |
25000 |
30000 |
| John |
31/03/2003 |
85000 |
40000 |
30000 |
40000 |
30000 |
| John |
30/04/2003 |
85000 |
40000 |
30000 |
40000 |
30000 |
| Emma |
31/01/2003 |
280000 |
40000 |
30000 |
0 |
- |
| Emma |
28/02/2003 |
280000 |
40000 |
30000 |
0 |
0 |
| Emma |
31/03/2003 |
280000 |
40000 |
30000 |
0 |
0 |
| Emma |
30/04/2003 |
280000 |
40000 |
30000 |
0 |
0 |