Edward Munya

Edward Munya

  • NA
  • 20
  • 0

Computations in Ms SQL

May 23 2013 11:04 AM
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




















































































































































Answers (1)