Calculating total of valuations based on varying percentage against a sum

[NiallFH]

Using MySQL 5.0

 

This seems a very complicated one.

 

Basic table structure:

 

val_contracts - contract_id, contract_ret (the % of retention the client will take)

 

val_valuations - valuation_id, valuation_con (corresponds with contract_id), valuation_worksvalue, valuation_date

 

This is a database of contracts. Each contract has valuations sent to customers each month, and within that contract is a % figure of retention (monies held by the customer) that they are expected to hold. For example:

 

Value of Work: £20000

Less Retention (5%): £1000

Payment Requested: £19000

 

As a job or contract progresses, the value of work will increase and also thereby the retention figure will increase, so therefore, at any one time, to gauge the amount of retention outstanding, the MOST RECENT Valuation for each contract is the only one I'm interested in.

 

What I want to do is to be able to show a total figure for ALL outstanding retention on ALL jobs/contracts. To do this, I need to write a query that will first find the most recent valuation (i.e. MAX(valuation_date)) and then somehow add together the value of work less retention (this % changes from client to client) for each job.

 

However, I am completely at a loss as how to do this. Can anyone offer some assistance?

[Jessica]

Can you write what you mean as a mathematical equation?

[NiallFH]

Can you write what you mean as a mathematical equation?

 

I'll give it a go!

 

At its most basic, it's something like:

 

sum(Value of Works * Retention %)

 

However that value of works figure needs to be taken from the most recent valuation for each contract, and I want the query to disregard anything older.

 

I.e.

 

Three contracts

 

Contract 1: Valuation 1 (Sep 2009), Valuation 2 (Oct 2009), Valuation 3 (Nov 2009)

Contract 2: Valuation 1 (Jul 2010), Valuation 2 (Aug 2010)

Contract 3: Valuation 1 (Nov 2009), Valuation 2 (Dec 2009), Valuation 3 (Feb 2010), Valuation 4 (Mar 2010)

 

The only ones I would want the value of works figure from are shown in bold:

 

 

Contract 1: Valuation 1 (Sep 2009), Valuation 2 (Oct 2009), Valuation 3 (Nov 2009)

Contract 2: Valuation 1 (Jul 2010), Valuation 2 (Aug 2010)

Contract 3: Valuation 1 (Nov 2009), Valuation 2 (Dec 2009), Valuation 3 (Feb 2010), Valuation 4 (Mar 2010)

[Barand]

something like this (not tested)

 

SELECT c.contract_id , SUM(valuation_worksvalue * contract_ret) as total
FROM val_contracts c
INNER JOIN val_valuations v ON c.contract_id = v.valuation_con
INNER JOIN (
  SELECT valuation_con, MAX(valuation_date) as latest
  FROM val_valuations
  GROUP BY valuation_con
  ) AS getlatest
ON v.valuation_con = getlatest.valuation_con AND v.valuation_date = getlatest.latest
GROUP BY c.contract_id